Pages

Thursday, June 24, 2021

Case conversion (%LOWER & %UPPER) in RPGLE - IBM i

Case Conversion

Built-In Function %XLATE has been very useful to convert the string from lowercase to uppercase and vice versa.

With the introduction of BIFs %LOWER (Convert to Lowercase) and %UPPER (Convert to Uppercase), this has been made much easier.*

%LOWER (Convert to Lowercase)

%LOWER converts the string passed (first operand) to lowercase. Part of the string can be converted to lower case by specifying optional start position and length operands.  

Syntax

%LOWER(string : start_position : length)

%UPPER (Convert to Uppercase)

%UPPER converts the string passed (first operand) to uppercase. Part of the string can be converted to upper case by specifying optional start position and length operands.  

Syntax

%UPPER(string : start_position : length)

Let's have a look at the example to understand these better. 

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

**Free

 

Dcl-S MixedCaseString Char(10) Inz('UpPeRcaSE') ;

Dcl-S LowerCaseString Char(10) ;

Dcl-S UpperCaseString Char(10) ;

 

// Converting the full string to lowercase

LowerCaseString = %Lower('LOWERCASE'); //lowercase

Dsply LowerCaseString ;

 

// Converting the portion of a string to lowercase

LowerCaseString = %Lower('LOWERCASE' : 2); //Lowercase

Dsply LowerCaseString ;

 

// Converting the portion of a string to lowercase

LowerCaseString = %Lower('LOWERCASE' : 2 : 4); //LowerCASE

Dsply LowerCaseString ;

 

// Converting the string to uppercase

UpperCaseString = %Upper(MixedCaseString); //UPPERCASE

Dsply UpperCaseString ;

 

*InLr = *On ;


In the above example, 
  • Line - 8: We are passing the string (in uppercase) to BIF %LOWER without any optional parameters.
    • This would convert the full string to lowercase. 
  • Line - 12: We are passing the string (in uppercase) to BIF %LOWER with an optional parameter 'start position'.
    • This would convert the string from start position till end of the string.
  • Line - 16: We are passing the string (in uppercase) to BIF %LOWER with both the optional parameters 'start position' and 'length'. 
    • This would convert the string from start position till the number of characters mentioned in the length parameter. 
    • Remaining string would be unchanged. 
  • Line - 20: We are passing a variable (with string in both lowercase and uppercase letters) to BIF %UPPER without any optional parameters. 
    • This would convert the full string to uppercase. 
  • Similar to the BIF %LOWER, Optional parameters can be used with %UPPER to convert part of the string to uppercase. 

*These Built-in Functions are only available since IBM i 7.4 TR4 and IBM i 7.3 TR 10.

If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form. 

Tuesday, May 11, 2021

Retrieve Job log information from SQL - IBM i

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 execution. 
  • NO (default) - An error is returned. 
  • YES - A warning is returned. No rows are returned when an error is encountered. 
Let's have a quick look at a query to retrieve the job log of current session. 

1

2

3

4

5

6

7

8

SELECT ORDINAL_POSITION,

       MESSAGE_TYPE, 

       MESSAGE_TIMESTAMP, 

       FROM_LIBRARY, 

       FROM_PROGRAM, 

       MESSAGE_TEXT, 

       MESSAGE_SECOND_LEVEL_TEXT 

FROM TABLE(QSYS2/JOBLOG_INFO(JOB_NAME => '*'))


In the above query, 
  • Lines 1 - 7: Few columns from the table function JOBLOG_INFO. More info on each of these columns can be found below. 
  • Line - 8: JOB_NAME parameter is being passed with '*' to retrieve the job log of current job. 
Below are the few columns used in the above query. 

ORDINAL_POSITION - A unique number of each row in the job log. First message in the job log would contain '1' and incremented by '1' for the next message. 

MESSAGE_TYPE - Type of message from the job log. Below are the valid values for this column. 
  • COMMAND
  • COMPLETION
  • DIAGNOSTIC
  • ESCAPE
  • INFORMATIONAL
  • INQUIRY
  • NOTIFY
  • REPLY
  • REQUEST
  • SCOPE
  • SENDER
MESSAGE_TIMESTAMP - Timestamp when the message was written to Job log. 

FROM_LIBRARY - The library containing the program or service program that sent the message.

FROM_PROGRAM - The program or service program that sent the message.

MESSAGE_TEXT - First level of text message. 

MESSAGE_SECOND_LEVEL_TEXT - Second level of text message. 

There are many other useful columns present in this table function. Full list of columns can be found here.  

Above query returns all the messages in the job log. We can also select the records based on the message type or any other columns as required.

1

2

3

4

5

6

7

8

9

SELECT ORDINAL_POSITION,

       MESSAGE_TYPE, 

       MESSAGE_TIMESTAMP, 

       FROM_LIBRARY, 

       FROM_PROGRAM, 

       MESSAGE_TEXT, 

       MESSAGE_SECOND_LEVEL_TEXT 

FROM TABLE(QSYS2/JOBLOG_INFO(JOB_NAME => '*'))

WHERE  MESSAGE_TYPE = 'DIAGNOSTIC' ;


In the above query, 
  • Line - 9: We are only retrieving the diagnostic messages by adding condition on MESSAGE_TYPE. 
Job log info from SQL - IBM i

This query would only return the diagnostic messages along with it's position in the job log (ORDINAL_POSITION) and timestamp the message was sent to job log (MESSAGE_TIMESTAMP).

In both the queries mentioned above, we are only retrieving the job log of the current job by passing '*' against the job name. 

Let's have a look at retrieving the job log of other job. 

1

2

3

4

5

6

7

8

SELECT ORDINAL_POSITION,

       MESSAGE_TYPE, 

       MESSAGE_TIMESTAMP, 

       FROM_LIBRARY, 

       FROM_PROGRAM, 

       MESSAGE_TEXT, 

       MESSAGE_SECOND_LEVEL_TEXT 

FROM TABLE(QSYS2/JOBLOG_INFO(JOB_NAME => '730621/REDDYP/QPAD202846'))


In the above query, 
  • Lines 1 - 7: Columns from the table function as required. 
  • Line - 8: We are passing qualified job name with job number, user and job name. 
By adding condition on the columns we can extract the specific information required. 

E.g.: List of commands executed in an interactive session. 

This can be achieved by adding condition on MESSAGE_TYPE. 

1

2

3

4

5

6

7

8

SELECT MESSAGE_TYPE, 

       MESSAGE_TIMESTAMP, 

       FROM_LIBRARY, 

       FROM_PROGRAM, 

       MESSAGE_TEXT, 

       MESSAGE_SECOND_LEVEL_TEXT 

FROM TABLE(QSYS2/JOBLOG_INFO(JOB_NAME => '730621/REDDYP/QPAD202846'))

WHERE  MESSAGE_TYPE = 'REQUEST' 


Job log information from SQL - IBM i

Above query can be amended to fetch the first command or last command etc. 

For some reason, if the job doesn't present in the system or any other errors occurs while running the above query, an error is returned. This may cause an issue when running these query as part of procedures or function.

To avoid this, we could consider using optional parameter IGNORE_ERRORS (YES). By passing this parameter, JOBLOG_INFO would only return a warning and no records are returned. 

1

2

3

4

5

6

7

8

9

SELECT MESSAGE_TYPE, 

       MESSAGE_TIMESTAMP, 

       FROM_LIBRARY, 

       FROM_PROGRAM, 

       MESSAGE_TEXT, 

       MESSAGE_SECOND_LEVEL_TEXT 

FROM TABLE(QSYS2/JOBLOG_INFO(JOB_NAME => '730621/REDDYP/QPAD202846', IGNORE_ERRORS => 'YES'))

WHERE  MESSAGE_TYPE = 'REQUEST' 


If this parameter (IGNORE_ERRORS) is not passed, default value 'NO' is considered and an error is returned for any error while running the query. 


If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form. 

Monday, May 3, 2021

Extract a portion of a Date/Time/Timestamp in RPGLE - IBM i

%SUBDT

Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times. 

This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT. 

Valid unit should be passed for the type of the value passed. Below are the valid values for each type.
  • Date - *DAYS, *MONTHS, *YEARS
  • Time - *HOURS, *MINUTES, *SECONDS
  • Timestamp - *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS

Syntax:

%SUBDT(value : unit { : digits { : decpos} })

  • Value and Unit are the mandatory arguments. 
  • Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp.
We can either pass the full form for the unit or use the short form. Below is the mapping of Full form to short form. 
  • *YEARS - *Y
  • *MONTHS - *M
  • *DAYS - *D
  • *HOURS - *H
  • *MINUTES - *MN
  • *SECONDS - *S
  • *MSECONDS - *MS
Let's have a look at couple of simple examples to see how this works. 

E.g.:

Extract Date, Month and Year from a Date field. 

Extract Date, Month and Year from Date using %SUBDT - RPGLE

In the above example, 
  • We are using two different date formats *ISO & *DMY which shows different number of digits for year (*ISO - 4 digits and *DMY - 2 digits). However, %SUBDT would always return the full year (4 digits) irrespective of the format of the date passed. 
    • Line - 17: %SUBDT with *ISO date format. 
    • Line - 22: %SUBDT with *DMY date format. 
  • We don't necessarily pass the date in a variable. 
    • Lines - 17 & 22: We are passing the date variables to extract the year. 
    • Line - 27: We are passing %DATE() to extract the month from the current date. 
    • Line - 32: We are passing the required date to extract the day from the passed date. 

Let's have a look at another example to extract Hours, Minutes and Seconds from a Time. 

Extract Hours, Minutes and Seconds from Time using %SUBDT - RPGLE

In the above example, 
  • Line - 17: We are using *HOURS full form for value instead of short form *H. Using any of these two would return the same result. 
  • Either a variable with time, %TIME() or a time literal can be passed to %SUBDT. 
    • Line - 15: A time variable is being passed to extract hours. 
    • Line - 20: %TIME() is being passed to extract minutes. 
    • Line - 25: Specific time can be passed directly by using time literal 't'. We are extracting seconds from the time passed. 

Let's have a look at the final example to extract the milli seconds by passing the Timestamp. We will also explore the two optional parameters in this example. 

Extract Seconds and Milliseconds from Timestamp using %SUBDT - RPGLE

Extracting Years, Months, Days, Hours and Minutes from Timestamp is same as extracting from Date and Time. Milliseconds can be extracted from Timestamp and optional parameters can be used on seconds to specify how many decimal positions are to be extracted for milliseconds. 

In the above example, 
  • Line - 14: Extracting milliseconds from timestamp. 
  • Line - 19: Extracting seconds from timestamp along with milliseconds by specifying optional parameters digits and number of decimal positions. 
    • We are passing digits (total) '6' and decimal positions '4'. %SUBDT would only return milliseconds up to 4 digits along with seconds. wSeconds variable has been defined with 6 decimal positions, so the last two decimal positions would be stored as '00'. 
    • If the third parameter (digits) is specified, Fourth parameter (decimal positions) must be specified. And, Fourth parameter should always be 2 less than third parameter (2 digits for seconds).
    • If optional parameters aren't specified, no milliseconds would be returned. 


If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form. 

Popular Posts