SQL on the iSeries – Part I (Intro)

Posted on by thejwal

SQL , without doubt, is one of the mandatory skills a modern day programmer should possess. The majority of database files on an iSeries server may be created using DDS (Data Description specification) but SQL is the future.

As most of you might be knowing, STRSQL is the command to start the interactive SQL session. The two main challenges iSeries professionals face when working with Databases are:

  1. Non Externally defined databases – These are the types of the files that do not have fields defined and the data is stored as single rows of data without any layout defined. To extract fields from such database files using SQL, the SUBSTR function has to be used.An example is as below:capture
  2. Dates not stored as date data types – Legacy applications and older databases used non standard date formats like CYYMMDD (eg: 1161211) or YYMMDD (eg: 120121) which need to be converted to standard formats.

Consider an example where the date 22nd Jan 2017 is stored as 1170122 in field INVDAT in CYYMMDD format. The SQL to convert this field into the DD/MM/YY format can be written as below:

SELECT SUBSTR(INVDAT,6,2) || '/' || SUBSTR(INVDAT,4,2) || '/' || SUBSTR(INVDAT,2,2) AS FORMATTED_DATE
FROM TABLE

This statement is pretty much self explanatory but for those who are entirely new to SQL, the below points are helpful:

  • Consider SUBSTR as a built in function which takes as arguments a string, a starting index and the number of characters to be extracted.
  • SUBSTR(INVDAT,6,2) translates to SUBSTR(1170122) which yields the returns two characters starting from the sixth character. 22 in this case.

Useful STRSQL tips 

There are a number of useful STRSQL tips which can be utilized heavily to ease day – to – day tasks. One is the re-directing of output from SQL statements to database files or Printer files. This can be done from the ‘Session’ by pressing F13. The session attributes can be set to send the output from a query to an output database file or to a printer file.

This was just a basic intro to SQL on the iSeries. In the next part, we consider embedding SQL statements within programs – what is termed as Embedded SQL.

 

Leave a comment