Selects with Intersect to find common records in two files

Having written about Selects with Union I thought it was time to mention to Selects with Intersect.

Select statements with the INTERSECT combines the matching results from two tables into one set of results. To reiterate "matching" means that the same value must appear in the given column(s) in both files.

I have two example tables with the same column names, they contain the following data:

Table 1	Table 2
ANGELA BETTY DOROTHY EVELYN GEORGINA	GEORGINA FRANCESCA EVA DOROTHY CAROLINE ANGELA

Writing to the same spool file from multiple programs

The question sounded simple: How can I write to the same spool file from two programs?

I have written before about copying from more than one spool file to create a new one. But this time I will be writing to the same spool file directly from two programs.

Let me start with the DDS for the printer file:

01 A R HEADER 02 A 1'PAGE HEADER' 03 A SKIPB(001) ***************************************************************** 04 A R FORMAT01 05 A 1'PROGRAM 1' 06 A SPACEB(001) 07 A COUNTER 3 0 +2EDTCDE(Z) ***************************************************************** 08 A R FORMAT02 09 A 1'PROGRAM 2' 10 A SPACEB(001) 11 A COUNTER R +2REFFLD(COUNTER *SRC) 12 A EDTCDE(Z) ***************************************************************** 13 A R ENDREPORT 14 A 1'* * * END OF REPORT * * *' 15 A SPACEB(001)

Subsystem status using SQL

Last year I wrote about a way to check if a subsystem was active or not. The statement combined two table functions and could only check if a subsystem was active, if it was not returned in the results it was assumed to be inactive. Included in the latest round of Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, is a new SQL View all about subsystems, now I can get the same information just from one View, including the status of the subsystem.

The information I am interested from the View SUBSYSTEM_INFO is:

• Subsystem name and the library it resides in
• Status: active, inactive, etc
• Number of jobs currently running in the subsystem
• Maximum number of jobs that could run in the subsystem
• Job that is monitoring the subsystem

Searching for reserved characters in SQL

SQL like all other programming languages has a number of characters that are reserved for certain uses. What happens if you need to search for one of those characters in a table or file? In this post I am going to give you two ways you can do this.

To show how this works I have a test file with five records in it:

FIELD1 ---------- HERE'S IT 100% REAL APPLE BEAR CAT

What I want to do is…

1. Find the record with the apostrophe ( ' ) in it
2. Find the record with the percent sign ( % ) in it

Using SQL to validate packed fields

When I heard of these three table functions being introduced as part of the latest round of Technology Refreshes, IBM i 7.4 TR2 and 7.3 TR8, I was really interested to know what they did as their names, "Validate data", really piqued my interested. These table functions will validate packed numeric fields at, what I like to call, three levels:

1. VALIDATE_DATA:  validates at the member level
2. VALIDATE_DATA_FILE:  validates at the file level
3. VALIDATE_DATA_LIBRARY:  validates at the library level

These three exist in the SYSTOOLS library.

It is nigh impossible to put bad data into date, time, and timestamp fields in files. But it is not too hard to put bad data into a packed numeric field. While rare, I am seeing this more as data is taken from other types of databases and applications, and loaded into files in IBM i.

Control progression of cursor in display file

The question was how to control the way the cursor moves from field to field on a display. The display had two columns of fields and the questioner wanted the cursor to progress down the first column's fields before moving to the top of the second column.

My example display file contains two records format. The first I will be using to demonstrate the default cursor progression, and the second how I can move down the first column's fields before moving over to the second column.

The DDS code for the first record format looks like:

A R SCREEN0 A Z001 3 B 2 3 A Z002 3 B 2 10 A Z003 3 B 3 3 A Z004 3 B 3 10 A Z005 3 B 4 3 A Z006 3 B 4 10

Deleting spool files using SQL

Many of the IBM i partitions I have worked upon have had an issue of old spool files occupying valuable storage space. Since IBM i 7.2 there has been a way for me to analyze the spool files, using SQL, and make decisions on which would be deleted. The process of doing the deleting had to be entrusted to a program containing CL commands.

The latest round of Technology Refreshes, 7.4 TR2 and 7.3 TR8, gave me a way I can delete old spool files using a SQL procedure, DELETE_OLD_SPOOLED_FILES

Warning:  Use this SQL procedure carefully. If you do not you run the risk of deleting all spool files from your partition.

DELETE_OLD_SPOOLED_FILES has five parameters:

Happy 7th birthday RPGPGM.COM

Another year has passed and this blog has reached its seventh anniversary. We are living in interesting times with IBM i, and over the years I have been fortunate to write about many things, that in my opinion, have made significant improvements to programming with this operating system. As well as a whole lot of cool additions to Db2 for i (SQL). I have learn a lot from what I have written about, and I am sure that I write better code than I use to.

The past year has been an exciting time, not only for this blog but for me too. I would include the following as my personal high points, in no particular order:

Making a hash of records for comparisons

Another addition to Db2 for i as part of the latest Technology Refreshes for IBM i 7.4 TR2 only, is the ability to create a hash value for a record from a file using the HASH_ROW SQL built in function. I am not going to describe what hash values are, if you are interested there is a Wikipedia article describing it. I am just going to say that hash-ing a string produces hexadecimal value of the data.

Previously I had been able to hash individual fields, or columns, using the various standards of hash. As HASH_ROW uses SHA512 I can do the same for a field using HASH_SHA512:

01 SELECT HASH_SHA512('A') AS "Hash", 02 LENGTH(TRIM(HASH_SHA512('A'))) AS "Length" 03 FROM SYSIBM.SYSDUMMY1 04 UNION 05 SELECT HASH_SHA512('ABCDEFGHIJKLMNOPQRSTUVWXYZ'), 06 LENGTH(TRIM(HASH_SHA512('ABCDEFGHIJKLMNOPQRSTUVWXYZ'))) 07 FROM SYSIBM.SYSDUMMY1

Using SQL to compare files for differences

Having looked at, and played, with a number of the new additions to Db2 for i in the latest new Technology Refresh this is my favorite and in my opinion the most useful: table function COMPARE_FILE.

COMPARE_FILE gives me the ability to identify differences between physical files, source files, and DDL (SQL) tables. I can compare two files layout and the data within them with this table function, and have a results row returned for each difference. Wow, both of those with the same table function!

Alas, it is only available in the Technology Refresh for 7.4 only, IBM i 7.4 TR2. It is not available in IBM i 7.3 TR8.

COMPARE_FILES has a number of parameters:

New SQL table function to display a job's locks

Before the latest round of new Technology Refreshes, 7.4 TR2 and 7.3 TR8, the only ways I knew to get Job Lock information was either by using an API or the Display Job command, DSPJOB. These new TRs introduced a new table function, JOB_LOCK_INFO, that allows me to see the same information with just a simple SQL statement.

JOB_LOCK_INFO has three parameters:

1. Job name: This is the only required parameter. This can either be the full job name, or for information about the current job an asterisk ( * ) can be used
2. Internal objects only: Optional. NO, which is the default, shows only external objects. YES shows internal objects, internal space objects, and external objects
3. Ignore error: Optional. NO when an error is encountered it is returned. YES, which is the default, only a warning is returned

Define a new Data Structure from one that is nested

In the past I have written about nesting data structures, a way to contain data structure(s) within another data structure. And there are times I have wanted to define a new data structure to be identical to another using the LIKEDS keyword.

01 dcl-ds One qualified ; 02 OneSF1 char(1) ; 03 dcl-ds Two ; 04 TwoSF char(5) ; 05 end-ds ; 06 OneSF2 char(1) ; 07 end-ds ; 08 dcl-ds Test1DS likeds(One) ;