Tag Archives: SQL

5 Reasons To Use SQL instead of DDS

Many of us have used DDS for years to define our databases. In recent years SQL has become more prominent on platforms other than the IBM i, but there still seems to be reticent to adopt SQL instead of DDS. Here are five reasons why you should consider using SQL instead of DDS from now on.  IBM also has a great redbook on SQL with lots of great tips on how to use SQL features and how to move from DDS to SQL.

Reason 1: Everyone else is doing it!
I know- when our kids come to us with this reasoning we usually dismiss their argument. However, it’s true that SQL is driving database design and management on almost all major platforms and software development environments.  By learning SQL you will have skills you can transfer to any development project and your employer will benefit with you having cross-platform skills.

Reason 2: IBM is continually enhancing SQL support on IBM i. They have stopped enhancing DDS.
IBM recognizes that SQL is the way of the future. DDS is a proprietary language, used only on the IBM i platform. Any time IBM adds new features to the database they add those features to SQL. For example, enhanced transaction processing is only available in SQL. Improved index management is only available in SQL

Reason 3: SQL described tables and indexes can often perform better than DDS logical files.
This is because DDS-described files check to rebuild/update indexes when data is read from a file, while SQL checks only when the underlying table is updated. Since most applications perform read-only operations 8 times more than read/write ops, this yields a performance advantage

Reason 4: SQL  DDL (Data Description Language) is easy to code, and many tools are available to help you.
With DDS you are limited to SEU or RDP  or BCD’s DbGen (which actually hides the coding for you) for describing your physical and logical files. With SQL there are many tools available, some open source. Or, coding it by hand is simple too.

Reason 5: You can phase in SQL tables and indexes with minimal impact on existing applications.
You can convert your physical and logical files to SQL tables and indexes without having to change the underlying code.  IBM provides a conversion command that lets you extract SQL table definitions from physical files to give you a head start.

Using SQL Performance Monitor To Find SQL Errors on IBM i

It’s difficult to find which program is producing an SQL error by looking in the joblog, especially for Apache instance jobs (CGI jobs). You can see the error, but you can’t find out the name of the program that issued it. To solve this problem, you can use SQL Performance Monitor tools. You can start an SQL Performance Monitor one of two ways:

  • Use System i Navigator, expand the Databases tree node for the system you are connected to, right-click on SQL Performance Monitors and create a new one.
  • Run command STRDBMON from a command line.

You can limit which jobs get monitored (with either approach). For example, if you want all the INTERNET Apache instance jobs, just specify a job name of INTERNET.

To end the monitor, use the same interface in System i Navigator, or use the ENDDBMON command.

Make sure you create the monitor to collect detailed, not summary information. You can also choose the file and library name in which to store the collected information.

Don’t leave the monitor running for too long- it has a negative performance impact on the system, and it writes lots of data quickly.

You can easily query the output table to find errors. For example, I started a monitor via the System i Navigator which output data to a table called QZG0000048 in library DUNCAN. Using STRSQL, you can find SQL errors and their corresponding program names. Use this query:

SELECT qqi8 as "sqlstate",qqc81 as "sqlcode",qqc104 as
"program library",qqc103 as"program name",qqc21,qq1000 FROM
qzg0000048 WHERE qqrid=1000 and qqi8 < 0 ORDER BY qqstim

There are lots of other fields in this file, too, such as job name, user name, etc. but be careful, because if you try to select all the fields for viewing using STRSQL your 5250 session might end abnormally – some of the fields contain binary data that is illegal to display.