Retrieve Data from DTAQ using SQL - IBM i

Retrieve data from Data Queue: 

Data Queues (DTAQ) has been very useful for communication between different Jobs on IBM i. 

There are multiple APIs to work with Data Queues. 

• Send Data Queue (QSNDDTAQ) - Write data to Data Queue
• Receive Data Queue (QRCVDTAQ) - Read data from Data Queue, This would clear the message from the Data Queue.
• Retrieve Data Queue Message (QMHRDQM) - Retrieve data from Data Queue, Multiple entries can be retrieved at once. This does not clear the retrieved messages from Data Queue.

In this post, we will see how to retrieve data from Data Queue in SQL using 'DATA_QUEUE_ENTRIES' Table function in QSYS2 with some examples. This would return the data similar to API 'QMHRDQM'. 

E.g.: 

Let's consider a Standard Data Queue created using below command with sequence 'FIFO'.

CRTDTAQ DTAQ(REDDYP1/TESTDTAQ) MAXLEN(20) SEQ(*FIFO) TEXT('Test Data Queue - FIFO')

This would return the Data in DTAQ along with the position. 

• Line 1 - We are only retrieving ORDINAL_POSITION (Position of the row in result data set) and MESSAGE_DATA (Message Received from Data Queue) in this example. We will see full list of columns available towards the end. 
• Line 2 - Table Function 'DATA_QUEUE_ENTRIES' is present in QSYS2. We don't always need to prefix it with library name if QSYS2 is present in SQL Path. 
• Line 3 - Parameter 'DATA_QUEUE', Data Queue Name to be passed.
• Line 4 - Parameter 'DATA_QUEUE_LIBRARY, Library Name in which Data Queue is present is to be passed. Below are the other two special values allowed.

*LIBL	Library List
*CURLIB	Current Library

Let's use the same example and retrieve the data in reverse order. 

This has returned the data in reverse order. If you notice, Ordinal Position hasn't changed because this would only represent how the data is retrieved in Result set and not how the data is present in DTAQ.

• Line 5 - Parameter 'SELECTION_TYPE' (Indicates how the messages are retrieved). This is an optional parameter with default value 'ALL'. Passing 'REVERSE' would return the data in reverse order. Below are some of the allowed values.

ALL	All messages in the Data Queue are to be returned and in the order Data Queue is created in (FIFO/LIFO).
FIRST	First message to be returned.
KEY	Messages are to be returned based on the Key data entered. KEY_DATA and KEY_ORDER should be passed along with this.
LAST	Last message to be returned.
REVERSE	All messages in the Data Queue are to be returned and in the reverse order. If Data Queue is created in FIFO, Data is returned in LIFO and vice versa.

Let's look at another example with a Keyed Data Queue. 

E.g.: 

Create a standard Data Queue with KEYED sequence with Key length of '2' using the below command. 

CRTDTAQ DTAQ(REDDYP1/KEYEDDTAQ) MAXLEN(20) SEQ(3KEYED) KEYLEN(2) TEXT('Test Data Queue - Keyed')

Data sent to data queue in the different order than the Keyed sequence. However, Data is retrieved in Keyed Sequence. To Retrieve the data without any Key comparison, Just passing Data Queue Name and Library Name should be sufficient. 

• Line 1 - Retrieving another column 'KEY_DATA' for Keyed Data Queue. 

In the above example, Third Entry has been written with Key Data '04' and Fourth Entry has been written with Key Data '03'. 

When the data is retrieved, Data is retrieved in the order of Key rather than the sequence data is entered into. 

Let's use the Selection Type, Key data and Key Order to retrieve the data based on the Key data passed. 

• Line 5 - Value 'KEY' is passed against the 'SELECTION_TYPE' to indicate the data to be retrieved based on the Key passed.
• Line 6 - Key data to be passed against 'KEY_DATA' parameter. We have passed '02' in this example.
• Line 7 - Comparison criteria to be passed against KEY_ORDER. We have passed 'GE' (Greater than or Equal To) in this example and all messages with Key data greater than or equal to '02' is retrieved. Below are the other values that can be passed to this parameter.

EQ	All messages with Key equal to Key data are to be returned.
GE	All messages with Key greater than or equal to Key data are to be returned.
GT	All messages with Key greater than Key data are to be returned.
LE	All messages with Key less than or equal to Key data are to be returned.
LT	All messages with Key less than Key data are to be returned.
NE	All messages with Key not equal to Key data are to be returned.

We have only retrieved columns ORDINAL_POSITION, MESSAGE_DATA and KEY_DATA in our examples above. Below are the other columns this function would return. 

Column Name	Data Type	Description
ORDINAL_POSITION	INTEGER	Postition of the row in the result data set.
DATA_QUEUE_LIBRARY	VARCHAR(10)	The library in which the data queue was found.
DATA_QUEUE	VARCHAR(10)	The name of the data queue.
MESSAGE_DATA	CLOB(64512)	The message received from the data queue as character data.
MESSAGE_DATA_UTF8	CLOB(64512) CCSID 1208	The message received from the data queue represented as character data in CCSID 1208.
MESSAGE_DATA_BINARY	BLOB(64512)	The message received from the data queue in binary form. This is the raw form of the data.
KEY_DATA	VARCHAR(256)	For a keyed data queue. the key value of the returned message. This is the actual key value, which could be different than the key-data parameter value.Contains the null value if this is not a keyed data queue.
MESSAGE_ENQUEUE_TIMESTAMP	TIMESTAMP	The date and time that the message was placed on the data queue.
SENDER_JOB_NAME	VARCHAR(28)	The qualified job name of the sender.Contains the null value if no sender information is available for the message.
SENDER_CURRENT_USER	VARCHAR(10)	The current user profile of the sender.Contains the null value if no sender information is available for the message.

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.

Run CL commands from SQL - IBM i

QCMDEXC (SQL Procedure):

On IBM i, CL (Control Language) has always been the best choice for writing the programs with the need for running lot of commands. 

Occasionally we get the need to run these commands from outside CL Programs (like RPG). Earlier I wrote about how to run these commands from RPGLE using QCMDEXC. Click Here to see how to run CL commands in RPGLE. 

With the introduction of QCMDEXC procedure, This can now be done in SQL.

This is a very useful addition while working with SQL Procedures, Functions, Triggers etc... 

There are various CL commands that can be run using QCMDEXC. First thing that comes to my mind is adding or removing a library from library list. 

E.g.: 

If we are running SQL Queries either using STRSQL from 5250 session or using Run SQL scripts from IBM i ACS, 

If we need to add or remove libraries, we either need to add or remove libraries outside of SQL (for 5250) or amend JDBC configuration (Run SQL Scripts).

With this, we could simply call 'QCMDEXC' (this procedure is present in QSYS2 library) and pass the required command as parameter. 

In our example, this would be 'ADDLIBLE' command. 

CALL QSYS2/QCMDEXC('ADDLIBLE LIBNAME')

This can be run inside SQL procedure either by passing the command directly or by preparing the command into a variable and passing variable as parameter. 

E.g.: 

We will take the same example of ADDLIBLE, But will prepare the command in a variable and pass variable as Parameter. 

In the above Example, 

• Lines 3 & 4 - Declaring the variables to store Library and Command to be Executed in a Stored Procedure.
• Line 6 - Assigning the Library name to variable. 
• Line 8 - Concatenating the Library name to 'ADDLIBLE' and assigning the final command to variable. 
• Line 10 - Pass Variable 'CMD_TO_EXEC' as parameter to QCMDEXC.

Alternatively, We do not need to prepare the full command always before calling the procedure. This can be concatenated and passed as parameter directly. 

In the above example, 

• In Line 7 - We are directly concatenating Library name and passing it as parameter to QCMDEXC. 

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form.

What is SQL Path? And, How to change SQL Path on IBM i?

SQL PATH:

SQL Path is an ordered list of libraries. Database manager uses this path to find any unqualified objects (like functions, stored procedures, variables...) except for operations like ALTER, CREATE, DROP, COMMENT, LABEL, GRAND or REVOKE on main object. 

Objects will be scanned from left to right on the library list (SQL Path) for its existence and first occurrence would be considered (for functions number of parameters should match as well along with name).

SQL Path for a job would be assigned based on the Naming convention used. 

• If the Naming Convention is '*SYS', SQL Path would be set to '*LIBL'. 
• If the Naming Convention is '*SYS', SQL Path would be set to 'SYSTEM PATH' along with User under which job is running on. System Path would be "QSYS","QSYS2","SYSPROC","SYSIBMADM"

This is applicable for both STRSQL (Interactive SQL) and Run SQL Session from IBM i ACS. 

For STRSQL, Naming Convention can be setup using 'NAMING' parameter on STRSQL command. 

For Run SQL Scripts, Naming Convention can be setup under 'Format' tab of JDBC Configuration.

Also, SQL Path used depends on the SQL statement. 

• For Static SQL statements, Path used is the value of the SQLPATH parameter on CRTSQL*** command. SQL Path can also be set using 'SET PATH' inside the program. Objects created by CRTSQL* command would store the SQL Path on the Programs, Modules, Procedures, Routines... and is composed based on the 'System Schema Name' at the time of creation. if this is changed later, These objects may need to be recompiled.
• For Dynamic SQL statements, Path used is from the CURRENT PATH special register. Current Path can be changed by using SET PATH.

'SET PATH' can be run inside the program or in Interactive SQL session. 

SQL Path can also be set inside a program using 'SET OPTION SQLPATH'. This can be used along with other parameters specified on SET OPTION inside a program. 

Library names can be specified directly by separating with comma (,) on PATH to change the SQL PATH. 

E.g.: 

SET PATH = LIBRARY1, LIBRARY2 

This would store the SQL Path as below in CURRENT PATH special register. 

"LIBRARY1","LIBRARY2"

Apart from specifying the library names directly, Below are the other ways to set up Path. 

SYSTEM PATH

Specified the schema names for the system path. This value is same as specifying schema names "QSYS","QSYS2","SYSPROC","SYSIBMADM".

SET PATH = SYSTEM PATH

SESSION_USER or USER

Specifies the value from SESSION_USER special register (User currently logged on). 

SET PATH = USER

SET PATH = SESSION_USER

CURRENT USER

Specifies the value from CURRENT USER special register.

SET PATH = CURRENT USER

SYSTEM USER

Specifies the value from SYSTEM USER special register. 

SET PATH = SYSTEM USER

Variable/Constant

Variable/Constant inside a program with the Library name(s) separated by commas can be used. 

Also, Multiple values can be setup at once. 

E.g.: If the SQL Path is to be setup with System Path followed by Session User, Both SYSTEM PATH and USER can be set. 

SET PATH = SYSTEM PATH, USER

There are few important points to note while setting up SQL Path. 

• A Library name should not appear more than once. 
• Number of Libraries allowed is dependent on the length of CURRENT PATH special register (3483 characters) and Maximum number of Libraries allowed are 268.

Authorization List on IBM i

Authorization List (AUTL):

Managing Authorities is the key part for any application. Authorities can be provided by User profile or associated Group Profile. One other way to do this is by using Authorization List. 

Authorization List allows multiple User profiles and/or Group profiles to be setup with the required authorities (*USE, *CHANGE, *ALL...) and add Authorization List to the corresponding Libraries or Object. 

This makes it easier to manage authorities to setup or change the authorities at one place rather than having to change on multiple libraries or objects. 

Apart from the fact that Authorization List makes it easier to manage authorities, There are couple of other major advantages of using Authorization Lists. 

• Authority can be granted or revoked even if the file is locked by adding or removing the user on Authorization List. Same cannot be done directly on a File even if it is open for Read.
• Authorization List provide a way to remember authorities when an Object is saved. And, Object will automatically be linked with Authorization List on Restore on to the same system. Only exemption to this is if ALWOBJDIF(*ALL), ALWOBJDIF(*AUTL) or ALWOBJDIF(*COMPATIBLE) is specified on the Restore command. 

It is advised not to maintain private authorities on the objects directly along with an Authorization List. Having this might affect system performance (by checking for authorities both on Object and Authorization List). 

How to setup Authorization List? This can be done in the 3 simple steps. 

1. Create the Authorization List. 
2. Add Users to Authorization List.
3. Attach Authorization List to Objects.

CRTAUTL (Create Authorization List) is used to create Authorization List. 

CRTAUTL AUTL(DATAAUTL) TEXT('Authorization List for Data Objects')

Existing authorization list can be seen by using WRKAUTL (Work with Authorization Lists)

Option '2' from 'Work with Authorization Lists' or 'EDTAUTL' (Edit Authorization List) are used to add or remove users and/or user's authorities. 

To attach Authorization List to Objects, one of the below commands can be used. 

• GRTOBJAUT (Grant Object Authority) - Use parameter 'AUTL' to add the Authorization List to Object.
• CHGAUT (Change Authority) - Use parameter 'AUTL' to add the Authorization List to IFS Object. 
• EDTAUT (Edit Authority) or EDTOBJAUT (Edit Object Authority) - Both these commands would display a screen and Authorization List to be entered against 'Object secured by authorization list'.

Data Area, Flat File (PF) & The Difference

Data Area and Flat File (Physical File) are two different types of objects which has their own advantages. Before we go about comparing these two, Let us see what is data area and flat file. 

Data Area (DTAARA):

A Data area is an object used to hold data for access by any job running on the system. A Data area can be used store the information of limited size. 

We can create the data area using CRTDTAARA (Create Data Area) command.

CRTDTAARA DTAARA(QTEMP/TEMPDTAARA) TYPE(*CHAR) LEN(2000) TEXT('Temporary Data Area')

Above command is used to create a data area of type character with length of '2000' digits. Maximum allowed length for Character data area is '2000' digits (Default is 32 digits).

CRTDTAARA DTAARA(QTEMP/TEMPDTAARA) TYPE(*DEC) LEN(24 9) TEXT('Temporary Data Area')

Above command is used to create data area of type decimal with length 24 digits, 9 decimal positions. Maximum allowed length for Decimal data area is 24 digits, 9 decimal positions (Default is 15 digits, 5 decimal positions).

There are some system defined data areas created automatically. 

• Local Data Area, usually referred as '*LDA' is created for each job in the system. Length of LDA is 1024 digits.
• Group Data Area, usually referred as '*GDA' is created by system when an interactive job becomes group job in the system (using the CHGGRPA - Change Group Attributes). Length of GDA is 512 digits.
• Program Initialization Data Area, usually referred as '*PDA' is created for each pre-start job. Length of PDA is 2000 digits. 
• Remote Data Area is data area on the remote system.

Below are the useful commands to work with data areas. 

Display Data Area (DSPDTAARA) is used to display the data in the data area along with attributes of data area (like Name, Length, Type and Description).

DSPDTAARA DTAARA(QTEMP/TEMPDTAARA)

Change Data Area (CHGDTAARA) is used to change the data in the data area. Data can be changed either for full data area or part of data area. Substring starting position and length to be defined if data in part of data area is to be changed. Substring starting position default value '*ALL' would change the data in the data area from starting position. 

CHGDTAARA DTAARA(QTEMP/TEMPDTAARA *ALL) VALUE('CHANGE DATA IN DATA ARAEA')

CHGDTAARA DTAARA(QTEMP/TEMPDTAARA (20 10)) VALUE('SUBSTRING')

Retrieve Data Area (RTVDTAARA) is used to retrieve the data in data area and store it in a CL variable. This command can only be used inside CL program. Similar to CHGDTAARA data can either be retrieved in full or substring. 

RTVDTAARA DTAARA(QTEMP/TEMPDTAARA *ALL) RTNVAR(&CLVARIABLE)

RTVDTAARA DTAARA(QTEMP/TEMPDTAARA (20 10)) RTNVAR(&CLVARIABLE)

Below are the some of the areas where Data areas are mostly used (not limited to) on IBM i.

• To pass the information within a job. This could either be done using '*LDA' or by creating data area in QTEMP.
• To provide a field that is frequently changes within a Job. 
• To provide a constant field to be able to access from different Jobs. 
• To prevent a process from being run by multiple users. Locking the data area to one user and checking if the data area is allocatable or not to determine if a process is running. 

Flat File (Physical File):

Flat file is actually a physical file (PF) with just one field (character). There is no need to define DDS to create flat file. Flat file can be created by using CRTPF and mentioning Record Length (RCDLEN).

CRTPF FILE(QTEMP/FLATFILE) RCDLEN(32766) TEXT('Flat File with Maximum Length')

Maximum allowed Record Length for Flat file is '32766' digits. 

Flat file can be used in the Programs similar to any other Physical files (to read, write, update and delete the data). File Name, Field Name and Record Format Name all are same.

Flat files are mostly used as Output file to copy the data to Stream file on IFS. 

Data Area vs Flat File:

So, What is the difference between Data Area and Flat File? These are two different object types and their usage is also different. It may not be appropriate to differentiate between Data Area and Flat File based on their usage. 

Here are some differences based on the attributes of Data Area and Flat File. 

• Data Area can only store the data up to 2000 digits maximum. Flat file can store the data up to 32,766 digits maximum.
• Data Area can only store one set of data. Flat file can hold more than one set of data as different records.
• Flat file can only be created with default character field. Data Area can be created as Character, Decimal or Logical. 
• Flat file created in QTEMP cannot be carried forward to the Submitted Job. Data in LDA would be carried to Submitted Job's LDA automatically.