CVTRPGFREE

 

CVTRPGFREE Version 1.5.00 is now available for download from SourceForge, here:

https://lnkd.in/gek-CTaX

SQL to find any interactive jobs

 SQL to find any interactive jobs

Quick SQL query (SQL400) to find any interactive jobs that are waiting for a reply to an inquiry message:

SELECT
    X1.JOB_NAME, X1.AUTHORIZATION_NAME, X3.program_name, X3.program_library_name
    FROM (select job_name, authorization_name from TABLE(QSYS2.ACTIVE_JOB_INFO(
           SUBSYSTEM_LIST_FILTER => 'QINTER')) x0 where x0.job_status = 'DSPW')
    AS X1,
   LATERAL
   (select * from table(qsys2.stack_info(x1.job_name)) x2
where x2.entry_type = 'OPM' order by ordinal_position desc limit 1 )
   AS x3
where (x3.program_name = 'QDMACCIN') and (x3.program_library_name = 'QSYS')

Reading Source Members in SQL400

Reading Source Members in SQL400

READSRC (Read Source File Member) is an SQL Table Function that accepts the source file library, file name, and member name input parameters and returns the Source Sequence number, Statement Changed Date, and the Source Statement Line itself. 

Suppose you want to view the contents of the last source member added to the QRPGLESRC source file. Using READSRC() you would specify the following

select * from table( sqltools.readsrc( 'yourlib', 'qrpglesrc', '*last'))

notes: yourlib (library name). you can use *last, *first, member name.

We have alternate traditional way to access source member by OVRDBF or CREATE ALIAS. But this new way helps user to access the source from anywhere like 5250, RDi, iNavigator etc.

 

Copy Data from Remote to Local #IBMi

how to copy data from a remote system to the local system using Db2 SQL.

The key is to use the 3-level naming support available in Db2 for i for about 10 years. Basically you can code something like the following to get to a remote location's database files:

SELECT * FROM CHICAGO.QIWS.QCUSTCDT;

Run this from SQL iQuery, the Command Entry screen, or from IBM ACS and it will pull in the records from the QCUSTCDT file on the remote server named CHICAGO.

To make this all work you need to have the remote location identified on your system. The WRKRDBDIRE command is used to setup these remote entries. Basically you give it a name, the IP address, and a couple of settings. For example, to setup the CHICAGO remote location, you might code the following:

ADDRDBDIRE RDB(CHICAGO) RMTLOCNAME('8.8.8.8' *IP) RMTAUTMTH(*USRID)

I used the *USRID option for remote authentication, but the default value may be best for your shop--basically this controls what is sent to the remote for the Authentication step (e.g., the equivalent of Sign On). When mixing IBM i release levels, as I always have to accommodate, then *USRID works best in my situation, but your situation would be different.

To pull in data from a remote file we combined the 3-level names with an INSERT statement. Consider the following INSERT statement that copies data from the remote CHICAGO location to the local system.

INSERT INTO MYDATA.CUSTOMERS

SELECT * from CHICAGO.MYDATA.CUSTOMERS WHERE BALDUE > 10000;

In this example, the customers located on the Chicago server are copied to the CUSTOMERS file on the local system when the remote record's BALDUE is over $10,000.

What about Functions? For example, one of our SQL iQuery customers has Power systems installed in nearly a dozen locations--all of which are on different land masses (i.e., islands). So the ability to drive there and check out the system isn't a frequent occurrence. I put together a little script that uses the #SQLTools DISK_LIST UDTF (you can use IBM's SYSDISKSTAT on V7R3 and later) to pull in the current status of the Disk drivers on those remote systems. The so called Protection Status column containing anything except 'ACTIVE' (e.g., DEGREDATED) is what we are looking for. IBM added PROTECTION_STATUS in a late TR on V7R3/R4 but not to R2 so if your disks are degraded SYSDISKSTAT won't help you know this.

When using a UDTF, you can't qualify it to 3-level name; that is chicago.sqltools.disk_list() will return a syntax error. But there is a work around!

You move the 3-level name to a dummy WHERE clause and the SQL processor realizes what you're doing and allows it. For example:

SELECT * FROM table( SQLTOOLS.DISK_LIST() ) d

WHERE EXISTS (SELECT * FROM CHICAGO.sysibm.sysdummy1);

This runs perfectly as expected!

Of course I realize if you're on V7R3 or later, then in the context of Disk Protection Status, you can easily use the following, instead:

select unit_number, resource_name, percent_used,protection_status

from chicago.qsys2.sysdiskStat;

Remember that a single physical system can contain multiple partitions (e.g., Production of DevOps). Three-level naming can be used between those partitions just the same. This provides an easy way to update data on the development partition when savrstobj isn't configured or isn't right for the situation.

Retrieve list of Open files for a Job from SQL - IBM i

 

Retrieve list of Open files for a Job from SQL - IBM i

The list of open files for a job can be seen by using the command WRKJOB (Work with Job) or DSPJOB (Display Job) and then by taking option '14'. Or, by using the API 'QDMLOPNF' (List Open Files). 

One other easier way to do this is from SQL by using the table function OPEN_FILES. 

This function accepts a single parameter 'Job Name'.

Job Name (JOB_NAME) - Name of the job (qualified) for which the list of open files are to be retrieved. If current job, '*' can be passed instead of the job name. 

One thing to note here is 'Job Control (*JOBCTL)' authority is required to retrieve the list of files opened by other user's jobs. And, no special authority is required to retrieve our own jobs. 

One advantage of having SQL table function to retrieve the the list of open files based on the specific condition as required. 

E.g.: 

• Retrieve the list of open physical files.        

• Retrieve the list of files opened in OUTPUT mode. 

• Retrieve the current RRN number of a specific file opened. 

and much more as required. 

Below are the some of the key columns present in this table function. 

Library Name (LIBRARY_NAME) - Name of the library that contains the open file.

File Name (FILE_NAME) - Name of the open file.

File Type (FILE_TYPE) - Type of the open file. Below are the valid file types.

• BSCF - Binary Synchronous Communications (BSC) file
• CMNF - Communications file
• DDMF - Distributed Data Management file
• DKTF - Diskette file (spooled and non-spooled)
• DSPF - Display file
• ICFF - Intersystem Communications Function file
• LF - Logical file
• MXDF - Mixed file
• PF - Physical file 
• PRTF - Printer file (spooled and non-spooled)
• SAVF - Save file
• TAPF - Tape file
• *INLINE - Inline data file

Member Name (MEMBER_NAME) - Name of the database member, If FILE_TYPE is physical (PF) or logical (LF). If multiple member processing is being performed, the value is *ALL.

Record Format (RECORD_FORMAT) - Name of the last record format that was used for an I/O operation to the file.

Activation Group Name (ACTIVATION_GROUP_NAME) - Name of the activation group to which the open file is scoped.

• *DFTACTGRP - The file is scoped to the default activation group.
• *NEW - The file is scoped to a *NEW activation group.
• Contains the null value for a file scoped to the job, not a specific activation group.

Thread ID (THREAD_ID) - Thread handle assigned by the system which identifies the thread in which the file was opened.

Open Option (OPEN_OPTION) - Type of open operation that was performed.

• ALL - The file was opened for all operations (input, output, update, and delete).
• INPUT - The file was opened for input operations only.
• OUTPUT - The file was opened for output operations only.

RRN (RELATIVE_RECORD_NUMBER) - Relative record number of the last record referred to by an I/O or open operation for database files.

For full list of columns present in this table have a look at this link. 

*This function is only available since IBM i 7.3 TR9 and IBM i 7.4 TR3.

Generate SQL for Database Objects - Part 2

 

Generate SQL Objects

In the previous post we have seen how to generate DDL statements for a specific database object passed using GENERATE_SQL procedure. 

In this post we will see how to generate DDL statements required to create group of objects at once using GENERATE_SQL_OBJECTS procedure. 

This procedure accepts a table (with list of database objects) as an input and generates the DDL statements for the objects listed in the table. 

Before we see how this procedure works, let's see how should we create the input table. 

Input table should be created with the column names mentioned below. 

• OBJECT_SCHEMA
• OBJECT_NAME 
• SQL_OBJECT_TYPE

OBJECT_SCHEMA (Object Schema/Library Name)

• Refers to the schema name of the object for which DDL is to be generated. 
• No special values (like *LIBL, *CURLIB) are allowed. 
• This would be ignored if the object type is schema. 

OBJECT_NAME (Object Name)

• Refers to name of object for which DDL is to be generated.
• Specific Name of Procedure or Function to be used for Procedures and Functions.
• For Table or View, Object must not refer to the alias. 
• Extract object name to be mentioned and no '%' is allowed to indicate the list of objects starting with specific name. 

SQL_OBJECT_TYPE (SQL Object Type)

• Refers to type of the object for which DDL is to be generated.
• Below are the valid SQL Object Types. 
• ALIAS - Object is an SQL alias.
• CONSTRAINT - Object attribute is a constraint.
• FUNCTION - Object is an SQL function.
• INDEX - Object is an SQL Index.
• MASK - Object is an SQL column mask.
• PERMISSION - Object is an SQL row permission.
• PROCEDURE - Object is an SQL procedure. 
• SCHEMA - Object is an SQL schema (or library).
• SEQUENCE - Object is an SQL sequence.
• TABLE - Object is an SQL table (or physical file).
• TRIGGER - Object attribute is a trigger. 
• TYPE - Object is an SQL type. 
• VARIABLE - Object is an SQL global variable. 
• VIEW - Object is an SQL view.
• XSR - Object is an XML schema repository object. 

Let's create the input table and add few database objects into this table.

We are using the same names as described above to create the input table. 

We are inserting objects of different types into the input table created. 

• Line - 7: Inserting the logical file 'TESTLF', library 'REDDYP1' and SQL object type 'INDEX'. 
• Line - 8: Inserting the specific name 'QSQGENSQLO' of SQL procedure 'GENERATE_SQL_OBJECTS', library 'QSYS2' and SQL object type 'PROCEDURE'. 
• Line - 9: Inserting the physical file 'TESTPF', library 'REDDYP1' and SQL Object type 'TABLE'.
• Line - 10: Inserting the schema (library) 'REDDYP1', library 'QSYS' and SQL Object type 'SCHEMA'. For schema, library name would be ignored. 

One thing to note here is duplicate entries are not allowed. 

We aren't adding objects in any specific order. However, GENERATE_SQL_OBJECTS procedure would generate the DDL statements for each object in the below order. So that if any dependent objects are present depended object would be created first (E.g.: Schema and Table or Table and View etc.) 

• Schemas
• Tables
• Sequences
• Aliases
• Non-MQT (Materialized Query Tables) tables, any constraints and indexes on these tables
• Functions
• Procedures
• Variables
• Views, Logical files, MQTs. and any constraints or indexes on these tables
• Triggers
• Masks
• Permissions
• XSR Objects

Let's now call the procedure GENERATE_SQL_OBJECTS by passing the input table with the list of database objects and see how the DDL statements would be generated. 

Below are the parameters we are using in the above query. 

SYSTEM_TABLE_NAME 

Name of the table with the list of database objects for which DDL statements are to be generated. See above for more details on how the table should be created. 

This is the only mandatory parameter. Default values would be considered for all other parameters if not passed. 

SYSTEM_TABLE_SCHEMA

Name of the schema in which the table (passed in the previous parameter) is present in. 

Default value 'QTEMP' would be considered if not passed. 

DATABASE_SOURCE_FILE_NAME

Source file name in which DDL statements are to be generated. 

Default value 'Q_GENSQOBJ' would be considered if not passed. 

DATABASE_SOURCE_FILE_LIBRARY_NAME 

Library name of the source file (passed in the previous parameter) in which DDL statements are to be generated. 

Default value 'QTEMP' would be considered if not passed. 

DATABASE_SOURCE_FILE_MEMBER

Member name of the source file (passed in the previous parameter) in which DDL statements are to be generated. 

Default value 'Q_GENSQOBJ' would be considered if not passed. 

If Database source file name, library and member aren't passed, statements would be generated in the default source member in QTEMP and the same would be returned as result set. 

If these values are passed, member should exist on the system. 

There are many other parameters we can consider passing based on the requirement and most of them are similar to GENERATE_SQL procedure.

Let's have a look at the DDL statements generated and the order they are generated. 

DDL for Schema is generated first.

DDL for Table is generated after Schema. 

DDL for Procedure is generated after Table.

DDL for Index is generated after Procedure.

All of these are generated in the single source member passed in the parameters. 

Full list of parameters can be found here. 

Original blog => https://www.ibmiupdates.com/2021/02/generate-sql-objects.html

Generate SQL for Database Objects - IBM i

 

Generate SQL

Data Definition Language (DDL) is used to create Database objects. 

If we have a database object and no corresponding DDL statements or If we have files created using DDS and needs to modify the files using DDL instead of DDS. 

SQL Procedure 'GENERATE_SQL' generates the DDL statements required to recreate the database object. 

This procedure offers flexible return methods and returns DDL statements either as a result set or to the specified source file member. 

Let's have a look at an example to see how this works. 

In the above example, we are generating the DDL source for the Physical File created using DDS (see below for DDS source). 

Before we see what this procedure returns, Let's have a look at the parameters passed. 

DATABASE_OBJECT_NAME - Name of the object name for which DDL source to be generated for. '%' can be specified if there are multiple objects starting with same name. 

DATABASE_OBJECT_LIBRARY_NAME - Name of the library name in which data base object is present in. '%' can be specified if the object is to be considered from multiple libraries starting with same name. 

DATABASE_OBJECT_TYPE - Type of the database object passed. Please note that this is SQL object type (E.g.: 'TABLE' is to be used if generating the DDL for Physical File). 

Below are some of the object types allowed. 

• TABLE - Object is an SQL table or Physical file. 
• VIEW - Object is an SQL view or Logical file.
• PROCEDURE - Object is an SQL procedure. 
• FUNCTION - Object is an SQL function. 

Full list of allowed object types can be found here. 

The above three parameters are mandatory and procedure would fail if not passed. 

Let's now look at the result set returned. 

The result set contains,

• Source Sequence Number.
• Source Date. 
• Source Data.

Same information is stored in table Q_GENSQL (member - Q_GENSQL) in QTEMP library. Table QTEMP doesn't necessarily present by the time procedure is run. If the table is already present, data in the member would be replaced. 

Let's now have a look at the DDS source used to create this Physical File originally. 

In the above example, we aren't passing the source file details to where the DDL statements should be restored. 

If we pass the source library, file and member details generated DDL statements would be updated to the corresponding source member. One thing to remember is if we are passing the Source file and member, this should be present by the time procedure is called, otherwise GENERATE_SQL would throw error.

We will have a look at generating DDL statements for the procedure 'GENERATE_SQL' itself and store the data into source member passed. 

In the above example, we are generating the source for 'GENERATE_SQL' procedure into the Source file passed. 

Let's have a look at the additional parameters we are passing. 

DATABASE_SOURCE_FILE_NAME - Name of the source file to which generated SQL statements to be added. 

• Source file passed should be present on the system. 
• Length of the source file should be greater than or equal to 92. 
• Name of the source file is case sensitive (E.g.: If I pass 'qsqlsrc' instead of 'QSQLSRC' an error would be thrown.

DATABASE_SOURCE_FILE_LIBRARY_NAME - Name of the library in which source file is present in. Library name is case sensitive and should be present on the system. 

Apart from the library name, This parameter accepts two special values mentioned below.

• *CURLIB - Current Library
• *LIBL - Library List

DATABASE_SOURCE_FILE_MEMBER - Name of the source file member to which generated SQL statements to be added. This should be a valid member name, case sensitive and should be present on the system. 

This parameter accepts two special values mentioned below. 

• *FIRST - First member of the source file passed.
• *LAST - Last member of the source file passed. 

REPLACE_OPTION - Replace option specifies if the source file member should be replaced or if the generated statements to be added to the existing member. 

Valid values are,

• 0 - Resulting SQL statements are added to the existing member. 
• 1 (default) - Existing member would be cleared before adding the generated SQL statements. One thing to note here is member may be cleared even if there is an error while processing. 

If SQL statements are being generated for more than one object at a time, only the source for last member would be remained in the member (If '1' is specified).

NAMING_OPTION - Naming convention to be used in the generated SQL statements. Below are the valid values. 

• SQL - SQL naming convention like schema.table
• SYS - System naming convention like library/file

While looking at the above examples, we found that '%' can be used for identifying a database object that starts with specific name or database object library that starts with a specific name.

What happens if there are more than one object matching with this criteria? 

This would depend upon the replace option. If the replace option is not specified or specified as '1', then SQL statements generated for the last object would be returned in the result set or updated in the source member passed. 

All the previous members would be replaced by the subsequence occurrence. 

In the above example, 

• Line - 1: We are using '%' to find the data base objects that start with 'TEST'. 
• Line - 2: We are using '%' to find the data base object specified in the libraries that start with 'REDDYP'. 
• Line - 4: Parameter 'REPLACE_OPTION' plays important role here. 
• If '0' is specified, SQL statements generated for each object found would be appended to the result set or source member (QTEMP/Q_GENSQL (Q_GENSQL) as no source file/member is passed). 
• If '1' is specified, SQL statements generated for each object would replace the existing data and will result in retaining the data for last object.

Apart from these, we are passing two additional parameters here. 

DROP_OPTION - Drop option specifies if the DROP statement should be generated before the CREATE statement. 

Valid values are,

• '0' (default) - DROP statement should not be generated. 
• '1' - DROP statement should be generated. 

CREATE_OR_REPLACE_OPTION - Create or Replace option specifies if the CREATE OR REPLACE statement should be generated or just CREATE statement. 

Valid values are, 

• '0' (default) - CREATE OR REPLACE should not be generated. 
• '1' - CREATE OR REPLACE should be generated. 

Result to the above query would only contain the source for the last object found and would contain both DROP statement and CREATE OR REPLACE statement. 

Apart from the parameters mentioned above there are many more useful parameters allowed by this procedure. Full list of parameters can be found here. 

Delete Old Spooled files from SQL - IBM i

 

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. 

Spool files usually doesn't occupy much system space and often ignored. But, they could occupy the considerable amount of time if the spool files hasn't been cleared for long time. 

Spool files can be deleted by using DLTSPLF (Delete Spooled File) command. 

One other way of deleting spool files is from SQL by using DELETE_OLD_SPOOLED_FILES procedure. This procedure is present in SYSTOOLS library.

One good thing about this procedure is there is an option to preview. 

Let's have a look at how this works. 

In the above procedure call, we are only passing one parameter. 

• 'PREVIEW' - By passing 'YES', this procedure won't delete any spool file and returns the list of spooled files matching with the selection. 

We have not passed any selection to delete the spooled files. So, procedure would consider the default parameters to delete or preview the spool files to be deleted. 

Below are the other parameters for this procedure. 

DELETE_OLDER_THAN -  Timestamp needs to be passed as a parameter. This would be considered as the starting point for deleting the spool files. Any spool file older than this timestamp would be considered for deletion. 

Default value for this parameter is 'CURRENT TIMESTAMP - 3 MONTHS'. If we don't pass this parameter all the spool files older than 3 months would be considered for deletion. 

P_OUTPUT_QUEUE_NAME - Name of the Output Queue. Spool files from this OUTQ would be considered for deletion. 

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files from all the OUTQs would be considered for deletion. 

P_OUTPUT_QUEUE_LIBRARY_NAME - Name of the library Output Queue is present in. Spooled files from all the output queues from this library would be considered for deletion (If OUTQ name has been passed only that specific OUTQ would be considered). 

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files from the OUTQs in all the libraries would be considered for deletion. 

P_USER_NAME - Name of the user whose spooled files are to be deleted. All the spooled files for this user would be considered for deletion.

Default value for this parameter is '*ALL'. If we don't pass this parameter spooled files for all the users would be considered for deletion. 

Let's have a look at an example by passing these parameters. 

In the above example, we are passing all the parameters and procedure would return the list of spooled files matching with the parameters passed. We can just pass the parameters that are required and leave the remaining to default. 

This would return the list of spooled files that are considered for deletion (PREVIEW => YES). 

Passing 'NO' to PREVIEW or removing PREVIEW parameter would delete these spool files. 

One thing to note is that, User should have sufficient authority to the spooled files to be deleted. 

• User should have authority to the DLTSPLF command. 
• User should have authority to the QSYS2/OUTPUT_QUEUE_ENTRIES_BASIC view.