Decoding Complexities

View Binding Directory Entries Binding directory is an object that holds the list of modules and/or service programs that may be required for the compilation of ILE programs.  In other words, Binding directory contains modules and/or service programs whose procedures might be used across different ILE programs and are required for compiling the ILE programs.  Can these programs be compiled without the use of binding directory? The answer is Yes. If a program is using procedures from one service program (say SRVPGM1), corresponding service program to be added against the parameter BNDSRVPGM when creating the program using CRTPGM.  So, Why is binding directory needed? In the above scenario, we are only talking about one program using procedure from one service program so it is easy to mention service program name while creating a program. Say if we have many procedures across different service programs (most common), It becomes hard to remember and adding all the service pr...

Spooled Files Spooled files are generated when when system writes (prints) the data to an Output Queue. These could be Reports generated by the programs using Printer File (PRTF), System dumps or Job logs.  Traditional way of getting the list of spooled files is by using WRKSPLF command, which by default displays the list of spooled files for the current user.  Same can be retrieved from SQL with the use of table function SPOOLED_FILE_INFO. This is available in QSYS2.  SELECT * FROM TABLE(QSYS2.SPOOLED_FILE_INFO()); Above query is simple and straight forward which returns the list of spooled files for the current user. Information returned is same as WRKSPLF (Work with Spooled files) and API (QGYOLSPL).  Let's have a look at how output of the query looks like.  This query returns a full list of columns (we will see all the columns towards the end) for the current user who is running the query.  We can make use of the parameters of the table function to retr...

Database files are key for any application. One can never imagine the impact of a database file being accidentally deleted.  There are ways to recover the data if the file is journaled . But, this would take lot of effort and business disruption.  With the introduction of RESTRICT ON DROP attribute in IBM i 7.5, we can restrict a file from being deleted accidentally.  Add Restriction on Drop To prevent a file/table from being deleted, ADD RESTRICTION ON DROP can be used with ALTER TABLE.  By adding this restriction a file cannot be deleted both by using DLTF (Delete File) from command line/CL program or by using DROP TABLE from SQL.  ALTER TABLE <Library>/<File> ADD RESTRICT ON DROP  E.g.:  This would add a restriction on REDDYP1/TEST and this file cannot be deleted. Once added, the file cannot be deleted.  Below error would appear if we try to delete the file from command line. Same would happen with DROP TABLE from SQL.  DSPFD (...

Job Queue Dealing with Job Queues (JOBQ) is part of day to day life when working with IBM i. There comes many situations where we need to check if a JOBQ is Held or Released, Which subsystem the JOBQ is attached to, No of active/held/released/scheduled jobs in JOBQ, Maximum number of active jobs etc.  Different commands (WRKJOBQ, WRKJOBQD, WRKSBSD) are available to view this information.  There is another easier way to access this information is by using SQL view JOB_QUEUE_INFO in QSYS2 (system name - JOBQ_INFO).  One thing to note is, User should either have read authority to the JOBQ or have one of the special authorities *JOBCTL, *SPLCTL. This view is helpful in many different ways like,  List of Job queues present in a library.  List of Job queues HELD/RELEASED in a library. Retrieve Subsystem a JOBQ is attached to.  List of Job queues in a Subsystem.  Retrieve maximum number of active jobs in a Job queue.  Retrieve number of Active/Held/Relea...

Record Locks Record locks often causes the programs to crash if not handled properly. Identifying which job is locking the record is essential to resolve the issue (specially if the locking job is interactive idle for long time).  One way of identifying the Record locks is by using the command DSPRCDLCK (Display Record Locks).  This command accepts the below parameters and allows checking for the locks at the file, member and record level. Output of this command can only be either display or print.                         Display Record Locks (DSPRCDLCK)                                                                                     Type choices, press Enter.          ...

Job log Job log provides information about the job including any errors and is extremely helpful to analyze what has happened.  Sometimes, it becomes difficult to check all the messages in the job log (especially when the job is running for longer or if there are many messages in the job log) just by using DSPJOB/WRKJOB.  SQL table function JOBLOG_INFO provides another way of accessing job log from SQL session. Each message from the job log is returned in a new row. This gives the flexibility to query and select the messages as required (E.g.: Requests, Diagnostic Informational etc).  This table function requires one mandatory parameter Job name and one optional parameter Ignore errors.  Job Name (JOB_NAME) - Qualified job name (job number/user/job name). For the job log of current job, '*' can be specified.  Ignore Errors (IGNORE_ERRORS) - This parameter accepts either 'YES' or 'NO'. This determines what needs to be done in case of any error in Query executio...