Getting the day of the week in a CL program using SQL

The germ for this post came from a Facebook post I saw. The question was posed: Is it possible to retrieve the day of the week in a CL program? Multiple people suggested using a CEE API. Then came a reply that really interested me. This person said he had built a SQL View for this purpose.

It struck me as a creative approach to this scenario. SQL has a global variable CURRENT_DATE which contains, as the name suggests, the current date:

SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1 00001 ---------- 2020-10-28

What kind of information would I want to know about today's date?

1. Day of the week (Wednesday)
2. Month name (October)
3. The full date (Wednesday October 28, 2020)
4. Day of the month (28)
5. Year (2020)
6. Day of the year (302)
7. Quarter (4)
8. Month number (10)
9. Week of the year (44)
10. Day of the week number (4)

I know that some of the above information could be retrieved from system values. I will explain why I am including this information in the view later in this post.

I am going to show the source code for this view in two parts. Below is the first part, the definition of the columns:

01 CREATE OR REPLACE VIEW MYLIB.CL_DATE_NAMES 02 FOR SYSTEM NAME "CLDATENAME" 03 AS 04 (SELECT CAST(UPPER(DAYNAME(CURRENT_DATE)) AS CHAR(9)) 05 AS DAYOFWEEK, 06 CAST(DAYOFMONTH(CURRENT_DATE) AS DEC(2,0)) 07 AS DAYOFMONTH, 08 CAST(UPPER(MONTHNAME(CURRENT_DATE)) AS CHAR(9)) 09 AS MONTHNAME, 10 CAST(YEAR(CURRENT_DATE) AS DEC(4,0)) AS YEAR, 11 CAST(RTRIM(UPPER(DAYNAME(CURRENT_DATE))) || ' ' || 12 RTRIM(UPPER(MONTHNAME(CURRENT_DATE))) || ' ' || 13 VARCHAR_FORMAT(DAYOFMONTH(CURRENT_DATE)) || ', ' || 14 VARCHAR_FORMAT(YEAR(CURRENT_DATE)) AS CHAR(28)) 15 AS FULLDATE, 16 CAST(EXTRACT(DOY FROM CURRENT_DATE) AS DEC(3,0)) 17 AS DAYOFYEAR, 18 CAST(EXTRACT(QUARTER FROM CURRENT_DATE) AS NUM(1,0)) 19 AS QUARTER, 20 CAST(EXTRACT(MONTH FROM CURRENT_DATE) AS DEC(2,0)) 21 AS MONTHNBR, 22 CAST(EXTRACT(WEEK FROM CURRENT_DATE) AS DEC(2,0)) 23 AS WEEK, 24 CAST(EXTRACT(DOW FROM CURRENT_DATE) AS NUM(1,0)) 25 AS DOWNBR 26 FROM SYSIBM.SYSDUMMY1) ;

Line 1: I always give my Views, Indexes, and Tables long descriptive names.

Line 2: As I can also give it a short system name that a CL program will use.

Line 4 - 26: This is the Select statement that builds the columns for the View. Every column has a Cast function. I am not going to give the reason why here, but I will later in this post. The Select is built over the table/file SYSDUMMY1 in the library SYSIBM. As the name suggests this is a dummy file that contains no meaningful data. None of the columns I am defining are related to this file. But a Select has to be built over a table/file and this is the dummy everyone uses.

Lines 4 and 5: I am extracting the day name from the current date using the DAYNAME function, and then converting the mixed case result, "Wednesday", to all upper case, "WEDNESDAY". On line 5 I am giving this column its name. The result is then cast to a fixed sized character column.

Lines 6 and 7: Extracting the day of the month and casting it to a decimal (packed) format.

Lines 8 and 9: Extracting the month's name and casting it as fixed sized character.

Line 10: Extracting year as decimal.

Lines 11 – 15: Here I am creating the formatted full date.

Lines 16 and 17: I am using the Extract function to extract the day of the year.

Lines 18 and 19: Extracting quarter and casting to a numeric/signed column.

Lines 20 and 21: Extracting month number.

Lines 22 and 23: Extracting week number.

Lines 24 and 25: I decided having the day of week as a number in the View would could be useful. The week starts on Sunday so it is day number 1. I am casting this single digit number to numeric in the view.

Line 26: Here is where I give the name of the dummy file.

The rest of the code for the View looks like:

27 LABEL ON COLUMN CL_DATE_NAMES ( 28 DAYOFWEEK IS 'Day of week', 29 DAYOFMONTH IS 'Day of month', 30 MONTHNAME IS 'Month name', 31 YEAR IS 'Year', 32 FULLDATE IS 'Full date', 33 DAYOFYEAR IS 'Day of year', 34 QUARTER IS 'Qtr of year', 35 MONTHNBR IS 'Month', 36 WEEK IS 'Week of year', 37 DOWNBR IS 'Day of week as No.') ; 38 LABEL ON COLUMN CL_DATE_NAMES ( 39 DAYOFWEEK TEXT IS 'Day of week', 40 DAYOFMONTH TEXT IS 'Day of month', 41 MONTHNAME TEXT IS 'Month name', 42 YEAR TEXT IS 'Year', 43 FULLDATE TEXT IS 'Full date', 44 DAYOFYEAR TEXT IS 'Day of year', 45 QUARTER TEXT IS 'Quarter of year', 46 MONTHNBR TEXT IS 'Month', 47 WEEK TEXT IS 'Week of year', 48 DOWNBR TEXT IS 'Day of week as number') ; 49 LABEL ON TABLE CL_DATE_NAMES IS 'CL_DATE_NAMES' ;

Lines 27 – 37: Here I am giving all the columns their column descriptions/headings.

Lines 38 – 48: And here the descriptions for the columns.

Line 49: Lastly I am giving this View a description.

As I am in the USA the day and month names are in English. The maximum length of the name of a day and a month is nine characters. If you use a partition that returns these values in another language you may need to adjust the size of the following columns:

• DAYOFWEEK
• MONTHNAME
• FULLDATE

How do I use this View in a CL program?

The Run SQL command, RUNSQL, does not allow me to retrieve data from the View using a Select statement. But as a View is just another file, and I can use the Receive File command, RCVF, to get the data. I would only need to receive the data from the View once. My CL program looks like:

01 PGM 02 DCLF FILE(CLDATENAME) OPNID(DTE) 03 RCVF OPNID(DTE) 04 DMPCLPGM 05 ENDPGM

Line 2: The declaration for the View. The Open Id, OPNID, parameter means that the fields from this View are prefixed with "&DTE_".

Line 3: I receive the data from the View. As I gave an Open Id in the declaration I have to use it here too.

Line 4: By using the Dump CL program command, DMPCLPGM, I am dumping all the values in the program's variables, including those from the View, into a spool file.

The dump generates the spool file QPPGMDMP. When I open it I find the values from the View's columns:

Variable Type Length Value ---------------- ----- ------- --------------------------- &DTE_DAYOFMONTH *DEC 2 0 28 &DTE_DAYOFWEEK *CHAR 9 'WEDNESDAY' &DTE_DAYOFYEAR *DEC 3 0 302 &DTE_DOWNBR *DEC 1 0 4 &DTE_FULLDATE *CHAR 28 'WEDNESDAY OCTOBER 28, 2020 &DTE_MONTHNAME *CHAR 9 'OCTOBER ' &DTE_MONTHNBR *DEC 2 0 10 &DTE_QUARTER *DEC 1 0 4 &DTE_WEEK *DEC 2 0 44 &DTE_YEAR *DEC 4 0 2020

Now I will explain why I included those values I could have retrieved from system values. If I had not included the day of the month and year in the View I would have had to have four additional lines in my program to get that information:

DCL VAR(&DAYOFMONTH) TYPE(*CHAR) LEN(2) DCL VAR(&YEAR) TYPE(*CHAR) LEN(2) RTVSYSVAL SYSVAL(QDAY) RTNVAR(&DAYOFMONTH) RTVSYSVAL SYSVAL(QYEAR) RTNVAR(&YEAR)

The value in the QYEAR is just the year part of the date, and does not include the century.

Why would I want to perform two additional commands when I can get the same information from the View?

The other point I said I would explain is why I cast all the columns in the View. It is for two reasons.

Firstly if I had not cast the columns and left them with the default sizes the columns would be:

Variable Type Length ---------------- ------------------ ------- &DTE_DAYOFMONTH Binary 9 0 &DTE_DAYOFWEEK Variable character 102 &DTE_DAYOFYEAR Binary 9 0 &DTE_DOWNBR Binary 9 0 &DTE_FULLDATE Variable character 290 &DTE_MONTHNAME Variable character 102 &DTE_MONTHNBR Binary 9 0 &DTE_QUARTER Binary 9 0 &DTE_WEEK Binary 10 0 &DTE_YEAR Binary 9 0

These sizes struck me as a bit too unwieldly for something as simple as information about today's date.

Secondly, while the data is in the View only when it is used it may not be a storage space utilization issue if the Day of the Month is in a 9,0 binary column, it is an problem for CL. When I ran the program without casting the columns I received the following error:

Message ID . . . . : CPF0863 Severity . . . . . : 40 Message type . . . : Escape Date sent . . . . : DD/DD/DD Time sent . . . . : TT:TT:TT Message . . . . : Value of binary data too large for decimal CL variable. Cause . . . . . : A binary field in the record has a value that is too large for the control language (CL) decimal variable declared for that field. A CL decimal variable of the length and decimal precision specified in the data description specification (DDS) for the file is declared for binary fields. Recovery . . . : Correct the data in the file that is not valid or, if the file is a data base file, use a logical view of the file that omits the field that is not valid.

Using the Cast function to define those columns as decimal and numeric prevented the error.

 

This article was written for IBM i 7.4, and should work for releases 7.2 and greater with the latest Technology Refreshes.