Get started
Bring yourself up to speed with our introductory content.
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
The SETOBJACC command
The SETOBJACC command loads data into memory, and with abnormal system end, will not prevent changed data from being written to disk.Continue Reading
Determining relationships between tables and files in DB2 UDB
There is no SQL command that can simply determine relationships between tables and files on your iSeries. If you have referential integrity constraints in place, you can write queries against the system catalog objects such as QSYS2/SYSREFCST to ...Continue Reading
SQL Updates with functions
How to code an SQL UPDATE on AS/400 with mathematical functions is explained by Kent Milligan.Continue Reading
SQL statement history storage
Hitting the wrong button in iSeries can cause a serious headache. If you have exited an STRSQL session without saving and want to retrieve your lost information, you may be out of luck. But Scott Ingvaldson explains where SQL statement history is ...Continue Reading
Copying IFS files with QCMDEXC as SQL stored procedure
ISeries PC/Windows connectivity expert Shahar Mor discusses how to copy IFS files to AS/400 with the QCMDEXC command.Continue Reading
Creating an SQL command to view two files as one
Expert Kent Milligan explains an SQL statement that will allow two separate transaction files to be viewed as a single file.Continue Reading
SQL packages on remote servers
ISeries expert Kent Milligan offers AS/400 advice on SQL packages to remote servers.Continue Reading
Fast guide to SQL on iSeries quiz
Is your SQL knowledge on iSeries lacking? Brush up on our fast guide to SQL on iSeries and follow it up with this quiz.Continue Reading
An example of using COUNT() in a STRSQL statement
iSeries expert Kenneth Graap gives an example of using COUNT() in a STRSQL statement.Continue Reading
Fast guide to iSeries SQL
Need SQL tips and advice? We've compiled some novice iSeries SQL tips and expert advice, as well as more advanced iSeries SQL information, in this Fast Guide to iSeries SQL. After you've spent some time with the material, test your knowledge with ...Continue Reading
Stored procedures allow you to balance network loads – Part II
In part two of this series on SQL stored procedures, Ron Turull explores how it can help optimize client/server applications, among other things, without bogging down the server. Isn't that the reason you went to client/server in the first place?Continue Reading
SQL defined database: Enjoy the view
Paul Tuohy provides a simple example to show you how SQL defined views may be of benefit in an existing environment. The easiest way to start using views is with iSeries Navigator, which is what I will be using in the following examples. I will also...Continue Reading
The iSeries Pocket SQL Guide -- Chapter 4
SQL concepts & facilities will help you to master iSeries SQL database development. Download your free chapter today.Continue Reading
Know-IT-All Chapter Quiz: SQL concepts & facilities
Think you know all there is to know about SQL? We challenge you to test your skills by taking our latest quiz.Continue Reading
SQL Built-In Functions and Stored Procedures -- Chapter 2
As a member of Search400.com, you're entitled to a complimentary copy of Chapter 2 of "Stored Procedures and Built-In Functions" written by Mike Faust and published by MC Press. This chapter, Built-In Functions" will explore the BIFs included in ...Continue Reading
Know-IT-All Chapter Quiz: SQL Built-In Functions
Think you know all there is about built-in functions? We challenge you to test your skills by taking our latest quiz.Continue Reading
Squirrel: The universal SQL client
Jim Mason, president of ebt-now, shares his thoughts on the open-source product Squirrel. According to Jim, Squirrel is a great tool for accessing any type of data anywhere using SQL. In this tip, he explains what Squirrel is, the challenges of ...Continue Reading
Bypassing the 65,000 row limit in Excel via the iSeries
When you need to upload more than 65,000 rows of data from the iSeries to Excel, use the "Receive files from Host icon". Learn how in this Search400.com tip.Continue Reading
Speed up SQL subselects
Here you'll find an easy way to speed up your SQL subselects.Continue Reading
Can commit and rollback be used on the iSeries?
Which is preferred: logical file or embedded SQL?
Calling an RPG LE program from a SQL trigger procedure
Locating the first day of the month in SQL
Here you'll find a simple way to locate the first day of the month in SQLContinue Reading
Accessing MS SQL Server database from the iSeries
An ITKE member recently was looking for some information on the best way to execute a SQL statement on the iSeries. Fellow iSeries users offered advice.Continue Reading
SQL for eServer i5 and iSeries -- Chapter 2
Learn how to use the SQL scripting tool included in iSeries Navigator.Continue Reading
Write results of SQL Select statement to an outfile
Outfiles are status quo on the iSeries, but did you know that SQL can produce them intrinsically? Ron Turull shows you how.Continue Reading
Getting truncated values on SQL Server
Access data on an SQL server from the iSeries
Audit the interactive SQL session
CHAR fields vs. VARCHAR
Run multiple SQL statements
Check constraint allows database admins to sleep a bit better at night
You can easily prevent errant programs from writing invalid data to a field with a simple constraint.Continue Reading
Physical Reads and Logical Reads
What ports need to be open within the firewall?
Developing a system with Delphi to connect to the DB2/400
Using INSTEAD OF triggers
First introduced in DB2 UDB V8.1, INSTEAD OF triggers are very useful to isolate applications from changes in database structure. Here's a tutorial.Continue Reading
Formatting the date with SQL
Here's a simple way to provide formatting for dates in DB2.Continue Reading
ROWID data type in V5R2
Multiple sorts without using dynamic SQL
You can change the order of your data sorted differently based on some condition with static SQL and only one cursor.Continue Reading
Recursive SQL
An example of how to use recursive SQL for "bill of material" applications on mainframe DB2 platforms.Continue Reading
Why do I keep getting SQL error SQL0204?
Can RUNSQLSTM run if you don't have SQL/400?
DB2 regular expressions for the SQL-challenged
A short (and wacky) introduction to regular expressions in DB2.Continue Reading
Working with triggers
A trigger in DB2 is a set of actions that will be executed when a defined event occurs. Here's a brief introduction.Continue Reading
Is your SQL optimized?
To check that your SQL is using the most optimized path, you should run EXPLAIN PLAN against the SQL.Continue Reading
Is it possible to access the results of a call to a stored procedure from the "green screen" side?
Generating update statistics
The following SQL will create a script for running full statistics on DB2 databases.Continue Reading
Find out what dynamic SQL is running
Look at all the dynamic SQL running in the database.Continue Reading
How can I transfer an XML file to a physical file?
Full text indexing in DB2/400
Unexpected results after doing OVRDBF then SQL
Store an image file in the iSeries
SQL/400 Developer's Guide
This book will help you create and manipulate comprehensive, complex, professional-quality SQL/400 databases.Continue Reading
SQL performance cost takeout tip
One easy cost-saving step is to check high cost DB2 plan explains for indexContinue Reading
Get the most out of your database
IBM's plans for DDS with DB2
SQL to select last day of month
Find the last date of a month with this DB2 tip.Continue Reading
Chose an AS/400 file member on the SQL SELECT statement
Preventing truncating trailing spaces
Saving interactive SQL sessions
Finding the time that a WRKOBJ command was input.
Using REUSEDLT on physical files
How to have the results of a Select statement written to an outfile