STRSQL - How to run SQL commands on any IBM iSeries (AS/400) in Batch/Dialog and that even without SQL/400 license ?

The STR(CON)SQL Toolkit uses the full functionality of the IBM built in SQL engine in any iSeries (AS/400). It is free and behaves very similar to STRSQL of the charged SQL/400 license program.

Have a look at the new and free STRSQL toolkit:

• Does not need the IBM SQL/400 License Program
• Supports the commands STRCONSQL and STRSQL
  This means, that it can be used as STRSQL on any iSeries, that does not have STRSQL installed. On other iSeries you would use STRCONSQL in order to make use of the further features.
• Behaves as the well-known STRSQL
• Is totally easy
• Very convenient user interface
• Supports all SQLs that are possible on iSeries
• Keeps all "old" SQLs per user (even after logging off and so on)
• Supports Unicode/ASCII tables
  (Therefore your job needs to be on a CCSID other than 65535. You can use
  CHGJOB CCSID(500) or
  CHGSYSVAL QCCSID to 037, 273, 500 or something similar)
• Example function for syntax help is available
• Supports dialog mode
• Supports batch mode
• Creation of print output
• Supports outfiles for the result sets
• Unfortunately no F4 Help possible
  (But as all old SQLs are kept, it is possible to get them with several times hitting F9 which should be mostly sufficient)

Installation information:
After you enter your name and mail adress you will receive a user and password for the download. This will work very fast.
You will download the zip compressed SAVF and the english documentation. Just follow the docu and you are fine.

Attention:
When you start the command STRSQL or STRCONSQL the first time, you have to select your preferred language. You should enter "01" for german or "03" for english and then you have to hit F6 for "Processing" as enter is not sufficient at this point. Later on in the toolkit you can always use enter or F6 for precessing.
Have fun with this nice tool !!!!

How the technique works in detail:

The interesting trick here is, that IBM built in the complete SQL engine in any iSeries (AS/400, AS400) and only created special license costs to the STRSQL frontend. If you are able, to code iSeries syntactically correct SQL code, you can use the following idea for running any SQLs on your server. You type them into a source file, compile them and run them on your server. The performance will be the same as with the expensive STRSQL. For sure, this is supported from the free SQLUTIL, which is available via a special PTF, as well. But, STRSQL & SQLUTIL can normally run in dialog only, if you don't want to code an RPG program around (only possible with STRSQL) it. So, you might run into performance problems on a box with limited interactive power. So, you might want to run them in batch anyway.

• First you create your source file with CRTSRCPF as follows:

  CRTSRCPF FILE(SAPTEST/QQMQRYSRC) RCDLEN(91)

• Now you use STRPDM or EDTF and enter the syntactical correct SQL statement in the member Z<table>:
  
  In our case:

  CREATE INDEX R3DATA/z<table> 
      ON R3<sid>DATA/<table> (<key1>, <key2>, ...)

• As the SQL is now available in a source file only we have to compile this to an executable object with CRTQMQRY:

  CRTQMQRY QMQRY(SAPTEST/Z<TABLE>) SRCFILE(SAPTEST/QQMQRYSRC)

• Finally we have to run this SQL script via STRQMQRY. As we do not want to waste our interactive performance, we might want to do this in batch as follows (but you can do it interactively as well):

  SBMJOB CMD(STRQMQRY QMQRY(SAPTEST/Z<TABLE>)) 
    JOB(Z<TABLE>) JOBQ(QS36EVOKE)

( STRCONSQL, STRSQL, SQLUTIL, SQL, batch, AS400, OS400, as400, os400, AS/400, OS/400 )