Odds and Ends

The end of the year is upon us and the holidays are fast approaching. That sounds like a good excuse for some odds and ends. I hope you find something helpful here.–Ted

Hey, Ted:

I’m writing in response to Michael Sansoterra’s article, Native Regular Expressions In DB2 For i 7.1 And 7.2. I don’t use regular expressions much, but our C# development team does. Here’s a regular expression that I find to be of help.

// count the slashes in the URL
Dcl-S Count     Packed(4);

Exec sql
   set :Count = REGEXP_COUNT('/qibm/ProdData/Java400/bin/JavaDoc','/') ;

–Steve

Three decades or so ago, when I began to develop my distaste for UNIX, I never imagined I’d see regular expressions in RPG. I have to admit, it beats a loop with %SCAN.–Ted

Hey, Ted:

I was working on a job scheduling program and needed various kinds of date information from the IBM i system. I came up with the following using SQL:

dcl-s wk# packed(2:0);   // Week number in the range from 1 to 53

dcl-s cwom# packed(2:0); // Current Week number in the month

dcl-s lwom# packed(2:0); // Last Week number in the month

dcl-s dow packed(1:0);   // Day of week 1 = Sunday, 7 = Saturday)

dcl-s dom packed(2:0);   // Day of the month 1 to 31.

dcl-s ldom packed(2:0);  // Last day of the month 28 to 31.

dcl-s bom date;          // Beginning of the month date

dcl-s eom date;          // End of the month date

dcl-s today date;

today = %date();

exec sql set option datfmt = *iso;
exec sql set :wk# = week_iso(:today);
exec sql set :dow = dayofweek(:today);
exec sql set :dom = dayofmonth(:today);
exec sql set :eom = last_day(:today);
exec sql set :ldom = dayofmonth(:eom);

bom = today - %days(dom - 1);

exec sql set :cwom# = week_iso(:bom);
exec sql set :lwom# = week_iso(:eom);

lwom# = lwom# - cwom# + 1;
cwom# = wk# - cwom# + 1;

–Mark

SQL has some powerful date-handling functions. There are lots of other powerful functions too, and all accessible from RPG!–Ted

Hey, Ted:

I came across an oddity. I’m sure it’s expected, but just not by me.

A former developer here created some IFS directories that begin with the # sign. For example:

/dir1/dir2/dir3/#1234567

I was in Qshell, and did a CD for each part of the path to get to the lowest level. It worked except when I did a CD for #1234567. I got an error:

cd: 001-0008 Error found changing to directory /home/MYSELF

After a couple of double-takes, I figured it was the # that caused the grief. When I did a cd with the full path, no problem. I got there as expected.

–Russ

Qshell interpreted that octothorpe as the beginning of a comment, Russ. In these Unix and Unix-like scripting languages, there is no difference between running a command in a shell or running a script. The same interpreter serves for both, so you can put comments in interactive commands. (You can do the same from a CL command line, BTW, but nobody ever does.) When you run the CD command without arguments, Qshell returns to your home directory.

To include the octothorpe, enclose the value in either single or double quotes.

cd "#1234567"
cd '#1234567'

–Ted

Hey, Ted:

Using SQL, how can I query a file that has a period in the name?

–Mark

Ah, yes, I remember my S/34 and S/36 days. I often wished for a tool like SQL!

To query that file, enclose the name in quotation marks.

select F00001, K00001, F00002
  from qs36f."J.MAST"
 where F00001 = 'A'
   and F00002 like '%3%'

You can also create an alias and query against that.

create alias QS36F/JMAST for QS36F/"J.MAST" (M060127)

select . . . from qs36f/jmast . . .

–Ted

Hey, Ted:

When I crank up RDi, the Commands Log tab in the Remote Systems Explorer (RSE) is empty. This means there is no box into which I can key a command until I run a command. How do I run a command if there is no box?

–Greg

Right-click on Objects under the connection in the navigation pane and select Run Command. . . . There is an awful lot to learn about RDi, but it’s worth it.–Ted

Hey, Ted:

The Delete Program (DLTPGM) command accepts a generic value, but not the special value, *ALL, for program name. I was able to use Remove Link (RMVLNK) to delete all the programs in a library.

rmvlnk '/QSYS.LIB/MYLIB.LIB/*.PGM'

–Tom

Hey, Ted:

We have two physical files, one before taking inventory, the other after taking inventory. The fields are the same in each file. We need to show anything in file 1 that is not in file 2, and also anything in file2 that is not in file 1. Can this be done in one SQL SELECT statement?

–David

You need a full exception join, which is normally done with a UNION ALL of a left exception join and a right exception join. Since you’re comparing rows (records), you can use one of those features we tend to forget about–EXCEPT.

 (select * from file1 except select * from file2)
union all
(select * from file2 except select * from file1)

The first SELECT finds the rows in FILE1 that are not in FILE2. The second SELECT finds the rows in FILE2 that are not in FILE1. UNION ALL puts them all together.–Ted

Hey, Ted:

The Display Program (DSPPGM) command shows me the source file and member from which a program was created. How do I see which source member a physical, logical, display or printer file was created from?

–William

I use the Work with Object (WRKOBJ) command. Enter option 8 and press F4 to prompt. Change the Detail field from *FULL to *SERVICE.

If you prefer, you can go straight to the same display this way:

DSPOBJD OBJ(mylib/myfile) OBJTYPE(*FILE) DETAIL(*SERVICE)

Hey, Ted:

I thought you might be able to use this. Did you know you can sort on the Work with Active Jobs (WRKACTJOB) display to show jobs that have a message waiting (for instance).

Put the cursor on the Status column and press F16. This sorts the column by status. Then press F7 and key MSGW into the string field and *STS into the column.

I learned this recently and thought it was very cool.

–Tricia

I did not know that, Tricia. Thanks for sharing that tip with the rest of us.–Ted

Hey, Ted:

Many of our RPG programs have hard-coded library names in SQL statements. Is there any way for me to select a library at runtime, or do I have to remove the qualification?

–Dennis

Believe it or not, a simple override will do the trick. Here’s an UPDATE statement in an RPG program.

exec sql
 update mylib/textfile
    set redfield = 'COMPLETE'
  where onefield = :Key;

Here’s an override:

OVRDBF   TEXTFILE   TOFILE(QTEMP/TEXTFILE)

The system updates the file in QTEMP. Who woulda thunk it?

Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.

RELATED STORY

Native Regular Expressions In DB2 For i 7.1 And 7.2