Access Client Solutions is a powerful tool for programmers. ACS replaces and enhances much of the functionality originally supplied by Navigatorfor i.

This session discusses how ACS can be used by programmers; including

• System Configuration
• 5250 Emulator
• SSH Terminal
• Integrated File System
• Printer Output
• Schemas
• Run SQL Scripts
• SQL Performance Center

You have started to master SQL but you have a sneaky suspicion that there might be more to it.

In this session you will learn valuable and time saving techniques with SQL that let you avoid writing code. Some of the topics covered include:-

• Functions of Note
• Grouping
  • Grouping-Sets
  • Super-Groups - ROLLUP and CUBE
• CASE
  • Using CASE
  • Pivot
• Subselect
  • Scalar
  • Quantified
  • Correlated
  • With INSERT, UPDATE and CREATE TABLE
  • With a Derived Table
• Common Table Expressions
• Union
• Identity Columns
• Row Change Timestamp
• A Word About Views

This session is based on the SQL functions available in the latest release of the OS and assumes you have a basic understanding of relational database concepts and SQL.

This session discusses, in detail, two of the more powerful features of DB2 for i - Triggers and Referential Integrity. Both provide powerful means of implementing business rules and data integrity and are a requirement for modern application.

As well as covering the required definitions, this session also shows how to handle constraint violations in RPG.

SQL is a data language that may be used to create, control, access, and manipulate Db2 for i databases. The data language is both simple to use and extremely powerful. Since it is the standard cross-platform language for accessing relational databases, it serves as a foundation for nearly all cross-platform and inter-language database access technologies, such as ODBC and JDBC.

SQL is also at the core of any modernization project - bot for defining the database and accessing/maintaining the data.

If you have been wondering what this language is like and what it can do, this session is for you.

Many RPG programmers have used interactive SQL as a tool to quickly browse data or to create test data scenarios, but have stopped short of embedding it into their RPG programs. Come to this session to learn why, when and how you may want to use SQL in conjunction with RPG. We will cover the basics of embedding SQL into RPG, including using SQL cursors, and a few tips on getting good performance from your SQL applications.

This session looks at:-

• Why use embedded SQL?
• Basic Syntax
• Using host variables
• Using a SELECT statement
• Using a Cursor
• Multi Row Fetch
• Identifying and handling errors
• The power of INSERT, UPDATE and DELETE
• Compile options
• When and how to use dynamic SQL
• Using PREPARE and EXECUTE
• Performance tips

You have been using embedded SQL in RPG. You have mastered the syntax, tried a few selects, declared a cursor, performed an update - you may even have tried some dynamic SQL!

Time to take it to the next stage and see what you can do with embedded SQL - beyond the basics

Topics covered in this session include:-

• Discussion of the key points in writing powerful embedded SQL
• What tools should you use to write and test SQL prior to embedding it?
• Controlling CRTSQLRPGI
• SQLCA and SQLCODE considerations
• Getting data without SELECT - GET DIAGNOSTICS and VALUES INTO
• Talking to Watson with Embedded SQL
• Accessing SQL Functions
• Identity Columns
• Mastering Multi Row Fetch
• SQL Exception Handler
• Dynamic SQL with SQL Descriptors
• Handling NULL

Through the use of Stored Procedures, SQL provides a means for other languages (and platforms) to take advantage of existing code. Stored procedures also provide a means of controlling access to your database as well as offering potential performance gains.

This session shows you how to code and create SQL Stored Procedures that utilize existing code (external) and how code and create Stored Procedures that are written in SQL.

The session will cover the following topics:-

• What are Stored Procedures and where and why you might use them
• Parameter styles
• Overloading procedures
• Stored Procedures
  • RPG programs to return one or more result sets
  • Returning result sets to the caller or the client
• SQL control statements (how to program in SQL)
• Writing stored procedures using only SQL
• Handling exception/errors in external and SQL routines