Get started

iSeries SQL commands and statements

• Running AS/400 query to find records based on date

  Learn about a quick way of running an AS/400 query to select records based on current sysdate. Continue Reading

• Tracking data changes on IBM i with triggers

  Triggers can be implemented on the AS/400 using a high-level language or SQL and can be used to enforce business rules or validating input data. Learn how to use DB2 triggers as a means of auditing data inserts, deletes or changes. Continue Reading

• Convert a numeric physical file to a character in SQL without leading zeros

  In SQL, when converting a numeric physical file to digits, pass the results of the DIGITS function through the STRIP function in order to avoid leading zeros. Continue Reading

• Inserting data from a CTE into a file in SQL

  To insert data from the CTE into a file in SQL, the common table expression definitions need to be declared within the nested SELECT statement when using a SQL insert statement. Continue Reading

• Running source member SQL statements with RUNSQLSTM

  The RUNSQLSTM command expects a semi-colon (;) at the end of each SQL statement. Continue Reading

• Enabling IBM i to access Microsoft SQL server data

  Microsoft SQL Server does not fully support the open group DRDA standard, and thus middleware is needed to enable the AS/400 to access Microsoft SQL server data.Continue Reading

• Using SQL to search within a DB2 field on IBM i

  By taking advantage of the Row_Number function introduced in IBM i's V5R4, you can process a range of values within a field.Continue Reading

• Selecting a PF with multiple members from SQL

  The simplest technique for selecting a PF with multiple members on AS/400 from SQL is to create an alias.Continue Reading

• Referencing a renamed column on SELECT clause in SQL

  The SQL Standard does not allow a renamed column on the SELECT clause to be referenced on a different part of the SQL statement such as the WHERE clause. To do it on a single statement, an SQL common table expression can be used.Continue Reading

• Using SQL on System i to create multicolored comment lines

  When using SQL to color source code on the AS/400, it is also possible to create multicolored comment lines. This System i tip provides the queries to do so.Continue Reading

• Joining files using SQLRPGLE

  In DB/400, for a join operation returning a single row, the SELECT INTO statement can be used in SQLRPGLE.Continue Reading

• Excluding characters from AS/400 DB2 SELECT statements prevents mapping errors

  In DB2 on AS/400, rows with characters that cannot be converted to a numeric value (A or #) need to be excluded from a SELECT statement in order to avoid a data mapping error when running the INT function on GLSBL.Continue Reading

• Using SQL syntax to override DB2 automatic column name generator on iSeries

  If you don't want to drop and recreate a table to change the internal names in your AS/400 DB2, a special SQL syntax can solve your problem.Continue Reading

• Making a file with an automatic key in iSeries with DDS keyword

  If you want to create an automatic key using a DDS keyword on AS/400, Kent Milligan explains that any native writes performed against an SQL table created with an identity table will result in DB2 generating identity values.Continue Reading

• Using SQL on System i to color source code and inline comments

  Without use of an advanced emulator, how do you color source code? SQL. This tip provides a complete table of valid hex values for display colors and the SQL queries to implement it on your system.Continue Reading