Pages

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. 

Thursday, April 15, 2021

Return Number of Parameters (%PARMS) and Parameter Number (%PARMNUM) - IBM i

Return Number of Parameters (%PARMS)

Built-In Function %PARMS returns the number of parameters passed to the procedure where %PARMS is run. 

This BIF is useful when the procedure contains multiple optional parameters and if the procedure is called from different places with different number of parameters to identify the number of parameters passed. 

This is most commonly used to to handle the procedure logic based on the parameters passed only. 

Value returned by %PARMS is same as *PARMS in Program Status Data Structure if used in the main procedure. 

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

%PARMS - RPGLE

In the above example, 
  • Lines - 5 to 9: Prototype declaration of the Sub procedure with one mandatory parameter, two optional parameters and one return value. We will see the code of procedure in a bit. 
  • Line - 18, 22 & 26: Calling the sub procedure with one mandatory parameter, two parameters and three parameters respectively. 
Let's have a look at the code for Sub procedure and see how the %PARMS work. 

%PARMS - RPGLE

In the above procedure, 
  • Lines 42 & 44: We are using %PARMS to determine the number of parameters passed. 
This is often used to handle the parameters (like initializing or making sure these aren't used in the logic etc) that are not passed and make sure the procedure works as expected. 

One thing to note here here, If the RTNPARM keyword is used return parameter would be considered as one of the (first) parameter and total number of parameters would be increased by 1. 

In the above example, If only one parameter is passed, %Parms would be 2 (by considering the return parameter). 

%PARMS with RTNPARM- RPGLE

In the above example, 
  • Line - 34: We are using RTNPARM keyword against the Procedure Interface. The same would need to mentioned in the Prototype as well. 
  • Lines - 42, 44 & 46: We are using %PARMS to check the number of parameters by considering the return parameter. 
    • %PARMS would be 2 if only one parameter is passed.
    • %PARMS would be 3 if two parameters are passed. 
    • %PARMS would be 4 if three parameters are passed. 

Return Parameter Number (%PARMNUM)

Built-In Function %PARMNUM returns the number of the parameter passed from the parameters list. 

Operand for this BIF should always be the parameter specified in the procedure interface. 

Below are the few important points to remember. 
  • %PARMNUM can only be used with the parameters defined in Procedure Interface (PI) and Parameter defined using PLIST cannot be used.
  • Parameter Name must be specified as it is defined in the PI. 
    • If the parameter is an array, Array should be used and not an Index. 
    • If the parameter is a data structure, Data structure should be used and not subfields.
    • If the parameter is a file, File should be used and not record format. 
  • If RTNPARM is used, Return value is considered as the first parameter and the other parameters would be considered next.
Let's have a look at an example without using RTNPARM first to understand how this works. 

PARMNUM - RPGLE

In the above example, 
  • Line - 34: Procedure Interface is defined without RTNPARM. 
  • Lines - 43, 46 & 49: %PARMNUM is used to get the parameter number passed. 
    • Only Parameter names from the Procedure Interface are to be used. 
  • Line - 52: %PARMS is used to return the total number of parameters passed. 
Similar to %PARMS, If we use RTNPARM total number of parameters would be increased by 1 and parameter number also would be increased by 1 as the return value is considered as first parameter. 

Let's add RTMPARM to the above procedure. 

PARMNUM with RTNPARM - RPGLE

By adding RTNPARM to the Procedure Interface, Number of the parameter and total number of parameters passed are increased by 1. 


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

Wednesday, April 7, 2021

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

Retrieve List of Open Files

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 list of open files from SQL - IBM i
  • Retrieve the list of files opened in OUTPUT mode. 
Retrieve list of open files from SQL - IBM i
  • Retrieve the current RRN number of a specific file opened. 
Retrieve list of open files from SQL - IBM i

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. 

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

Wednesday, March 31, 2021

Check locks on IFS Objects from SQL - IBM i

IFS Object Locks

Identifying locks or references on IFS Objects become essential at times. This can be done by calling API QP0LROR (Retrieve Object References). 

The same information can be retrieved from SQL by using table function IFS_OBJECT_LOCK_INFO. 

This function accepts two parameters (Path name and Ignore errors). 

Path Name (PATH_NAME) - Path name for which the locks or references are to be retrieved. This could be a directory or stream file. This is a mandatory parameters. 

Ignore Errors (IGNORE_ERRORS) - YES or NO to be passed to determine what needs to be done when an error is encountered. This is an optional parameter and YES would be considered if no value passed. 
  • YES (Default) - A warning is returned and no row would be returned incase of an error.  
  • NO - An error is returned.

This function can be called to check the locks on a directory

IFS Object locks from SQL - IBM i

Or, by passing a stream file. 

IFS Objects locks from SQL - IBM i

Optionally we can pass Ignore errors 'NO' if the error is to be returned in case of any errors (like Invalid directory or stream file). 

Below are the few important columns returned. 
  • PATH_NAME - Full path name of an object.
  • JOB_NAME - Job name holding the lock or reference. 
  • RO_COUNT - Number of read only references for this job.
  • WO_COUNT - Number of write only references for this job.
  • RW_COUNT - Number of read and write references for this job. 
  • XO_COUNT - Number of execute only access references for this job. 

There are many other columns returned by this function and full list of columns can be found here

*This function is only available since IBM i 7.3 TR7 and IBM i 7.4 TR1. 

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

Wednesday, March 24, 2021

Check Journaled Objects from SQL - IBM i

Journaled Objects

Journals play very important role on IBM i and are helpful to identify who has updated the data and/or to retrieve the data before the update or delete from the tables and so on. 

Journaled objects can be seen by using WRKJRNA (Work with Journal Attributes) CL command or by using API QjoRetrieveJournalInformation (Retrieve Journal Information). 

One other easier way to check the list of journaled objects is by using SQL view JOURNALED_OBJECTS. This view is present in QSYS2 and system name JRN_OBJS.

We can use this view to retrieve the objects attached to a particular journal or identify the journal associated with the particular object and so on. 

E.g.: 

Retrieve the objects attached to a journal. 

Retrieve Objects attached to a journal - IBM i

In the above query, 
  • Line - 1: SELECT statement on JOURNALED_OBJECTS view. 
  • Line - 2: Where condition on column JOURNAL_LIBRARY to select the records with the journal library passed. 
  • Line - 3: condition on column JOURNAL_NAME to select the records with the journal name passed. 

Or, If we need to identify the journal attached to a specific object. 

Identify the journal attached to a object - IBM i

In the above query,
  • Line - 2: Where condition on column OBJECT_LIBRARY to select the records with objects present in the library passed. 
  • Line - 3: condition on column OBJECT_NAME to select the records with the object name passed. 
  • Line - 4: condition on column OBJECT_TYPE to select the records with the object type passed. 

These are just the two examples that I use most and we can query for the specific requirement from the different columns present in the view. 

Below are the full list of columns present in this view. 

  • JOURNAL_LIBRARY - The name of the library that contains the journal. 
  • JOURNAL_NAME - Then name of the journal.
  • IASP_NUMBER - The number of ASP (Auxiliary Storage Pool) in which journal is present (1 for the system ASP).
  • IASP_NAME - The name of IASP (Independent Auxiliary Storage Pool). Special value *SYSBAS for system ASP (ASP 1) and other basic ASPs (2 - 32).
  • OBJECT_TYPE - Type of the object journaled. 
    • *DIR - Directory
    • *DTAARA - Dataarea
    • *DTAQ - Data Queue
    • *FILE - Database File
    • *JRNRCV - Journal Receiver
    • *LIB - Library
    • *STMF -Stream File
    • *SYMLNK - Symbolic Link
  • OBJECT_LIBRARY - Name of the library in which the journaled object present. Contains null value for object types *DIR, *STMF or *SYMLNK.
  • OBJECT_NAME - Name of the object journaled. Contains null value for object types *DIR, *STMF or *SYMLNK.
  • FILE_TYPE - Type of the file that is journaled. Below are the valid file types and contains null value if the object journaled is not a file.
    • PHYSICAL - Physical File
    • LOGICAL - Logical File
  • PATH_NAME - IFS path name for the IFS object journaled.  Contains null value if object types are not *DIR, *STMF or *SYMLNK.
  • FILE_IDENTIFIER - Identifier associated with the IFS object. Contains null value if object types are not *DIR, *STMF or *SYMLNK.
  • JOURNAL_IMAGES - Specifies the kind of images to be written to journal for this object. Below are the valid values and contains null value for journal receiver (Object type - *JRNRCV).
    • *AFTER - Only after images are written to the journal.
    • *BOTH - Both before and after images are written to the journal.
  • OMIT_JOURNAL_ENTRY - Specifies the journal entries to be omitted from writing to journal for this object. Below are the valid values and contains null value for journal receiver (Object type - *JRNRCV).
    • *NONE - No entries are omitted. 
    • *OPNCLO - Open and close entries on the specified file members are omitted. 
    • *OPNCLOSYN - Open, close and force entries on the specified objects are omitted. 
  • INHERIT - Specifies whether new objects created within this journaled library (or directory) should inherit the journal state of the parent library (or directory). Below are the valid values and contains null value if object type is not *LIB or *DIR.
    • *YES - New objects will inherit the journal state of the parent. 
    • *NO - New objects will not inherit the journal state of the parent.
  • REMOTE_JOURNAL_FILTER - Specifies whether the journal entries written for the objects that inherit the parent library (or directory) are eligible for remote journal filtering by object. Below are the valid values and contains null value if object type is *JRNRCV.
    • *YES - Journal entries written are eligible for remote journal filtering. 
    • *NO - Journal entries written aren't eligible for remote journal filtering.

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

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

Wednesday, March 17, 2021

Split the string in SQL - IBM i

Split String

Substring from a string can be extracted using %SUBST BIF in RPG, %SST in CL and SUBSTRING in SQL by passing from position and the numbers of characters to be extracted. 

But, if we need to split the string based on a specific delimiter and if we don't know the positions? One way to do this is by retrieving the position of a delimiter in the string using %SCAN BIF. This would require more logic to be written if the string needs to be split into multiple substrings. 

This has been made easier with the use of SPLIT table function in SQL. This function is present in SYSTOOLS library.

SPLIT table function accepts three parameters. 
  • INPUT_LIST (Input List) - String(s) that needs to be split.
  • DELIMITER (Delimiter) - String or character that needs to be considered as separator. Both Input List and Delimiter are mandatory parameters. 
  • ESCAPE (Escape) - A character string of length '1' that is to be used as escape character. Delimiter followed by escape character would not be considered as separator. This is an optional parameter.

Let's have a quick look at the simple example to understand this better. 

E.g.:

Split the string into words (consider blank space as delimiter). 

Split string into words in SQL - IBM i

In the above query, we are only passing the two mandatory parameters Input list and Delimiter. 

This table function returns the below two columns. 
  • ORDINAL_POSITION (Ordinal Position) -  Position of the substring in the result. Starts from 1.
  • ELEMENT (Element) - Substring that is split. 
Result of the above query would be as below. 

Split string in SQL - IBM i

Above query has split all the words followed by space and returned as a separate row. 

In case if we need some of the delimiters to be skipped followed by a specific (escape) character, we can pass the optional parameter ESCAPE with the corresponding character. 

Split the string in SQL - IBM i

In the above example, 
  • We are passing back slash (\) as escape character. 
  • Table function would ignore the delimiter followed by the escape character and would not split the the string. 
  • Escape character wouldn't present in the substring split. 
Split string in SQL - IBM i

In both these examples, we are passing the string exclusively which may not always be the case if we need to use this function in the procedures or programs. 

We can use pass the columns from the other tables in query and specify the delimiter. 

Let's have a look at another simple example. 

Split string in SQL - IBM i

In the above query,
  • Line - 1: TESTTABLE is a table with just one character column (CHARFIELD). 
  • Line - 2: Table function SPLIT from SYSTOOLS library. 
  • Line - 3: First parameter INPUT_LIST for SPLIT table function. We are passing one column CHARFIELD from the table TESTTABLE. We are using TRIM so any leading or trailing blanks wouldn't be considered.
  • Line - 4: Second parameter DELIMITER for SPLIT table function. We are passing blanks, so string would be split into words like in previous example.
  • We aren't passing the third and optional parameter ESCAPE. This can be passed if required. 

Let's have a look at the result.

Split string in SQL - IBM i

In the result, 
  • First column CHARFIELD is from the table TESTTABLE, original data in the table before it is split. 
  • Second column ORDINAL_POSITION returns the position of the substring. 
  • Third Column ELEMENT returns the substring. 
There would be multiple rows of substrings for each row in the original table. 

*This function is only available since IBM i 7.3 TR6 and higher. 

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

Popular Posts