Pradeep Panga

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 -...

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...

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 set...

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 wh...

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 dec...