Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1655
  • Last Modified:

Samples SQL DB2 AS400 iSeries R5V3M0

I had a HARD time finding samples for SQL DB2 AS400 iSeries R5V3M0.
So I'll add a few to the Internet-Cloud here in EE.
Enjoy.
0
volking
Asked:
volking
  • 7
1 Solution
 
volkingAuthor Commented:
Create a Table

------------------------------------------------------------------------
CREATE TABLE MYLIBNAME.YMD (
      DAYDATE DATE DEFAULT NULL ,
      YYYY INTEGER DEFAULT NULL ,
      MM INTEGER DEFAULT NULL ,
      DD INTEGER DEFAULT NULL ,
      MON3CHAR CHAR(3) CCSID 37 DEFAULT NULL ,
      MONNAME VARCHAR(10) CCSID 37 DEFAULT NULL ,
      DAY3CHAR CHAR(3) CCSID 37 DEFAULT NULL ,
      DAYNAME VARCHAR(10) CCSID 37 DEFAULT NULL ,
      YY2CHAR CHAR(2) CCSID 37 DEFAULT NULL ,
      MM2CHAR CHAR(2) CCSID 37 DEFAULT NULL ,
      DD2CHAR CHAR(2) CCSID 37 DEFAULT NULL ,
      YYYYMMDD INTEGER DEFAULT NULL ,
      YYYY_MM_DD CHAR(10) CCSID 37 DEFAULT NULL ,
      FISCALYYYY INTEGER DEFAULT NULL ) ;
------------------------------------------------------------------------
LABEL ON TABLE MYLIBNAME.YMD
      IS 'YYYYMMDD table for easy JOINS to date stuff' ;
0
 
volkingAuthor Commented:
Create an Index on a table

CREATE UNIQUE INDEX MYLIBNAME.YMD_INDX
      ON MYLIBNAME.YMD ( DAYDATE ASC ) ;
------------------------------------------------------------------------
COMMENT ON INDEX MYLIBNAME.YMD_INDX
      IS 'Indexes YMD on DAYDATE' ;
0
 
volkingAuthor Commented:
Create a Stored Procedure
This one will populate the YMD TABLE declared above

CREATE PROCEDURE MYLIBNAME.YMD_BUILD ( )
      LANGUAGE SQL
      SPECIFIC MYLIBNAME.YMD_BUILD
      NOT DETERMINISTIC
      MODIFIES SQL DATA
      CALLED ON NULL INPUT
      P1 : BEGIN
      DECLARE DDAYSDATE DATE DEFAULT '01/01/1950' ;
      DECLARE DDATEEND DATE DEFAULT '01/01/2051' ;
      DECLARE CYYYY CHAR ( 4 ) ;
      DECLARE CYY CHAR ( 2 ) ;
      DECLARE CDD CHAR ( 2 ) ;
      DECLARE CMM CHAR ( 2 ) ;
      DECLARE IYYYY INTEGER ;
      DECLARE IYY INTEGER ;
      DECLARE IDD INTEGER ;
      DECLARE IMM INTEGER ;
      DECLARE IIYMD INTEGER ;
      DECLARE EDAY1 DECIMAL ( 8 , 0 ) DEFAULT 1 ;
      DECLARE CMON3CHAR CHAR ( 3 ) ;
      DECLARE VMONNAME VARCHAR ( 10 ) ;
      DECLARE CDAY3CHAR CHAR ( 3 ) ;
      DECLARE VDAYNAME VARCHAR ( 10 ) ;
      DECLARE VYYYY_MM_DD VARCHAR ( 10 ) ;
      DECLARE IFISCALYYYY INTEGER ;
      DELETE FROM MYLIBNAME . YMD ;
      WHILE DDAYSDATE < DDATEEND DO
            SET IYYYY = YEAR ( DDAYSDATE ) ;
            SET IMM = MONTH ( DDAYSDATE ) ;
            SET IDD = DAY ( DDAYSDATE ) ;
            SET CYYYY = CAST ( IYYYY AS CHAR ( 4 ) ) ;
            SET CYY = SUBSTR ( CYYYY , 3 , 2 ) ;
            SET CMM = RIGHT ( '0' || CAST ( IMM AS VARCHAR ( 2 ) ) , 2 ) ;
            SET CDD = RIGHT ( '0' || CAST ( IDD AS VARCHAR ( 2 ) ) , 2 ) ;
            SET IIYMD = CAST ( ( CYYYY || CMM || CDD ) AS INTEGER ) ;
            SET VDAYNAME = DAYNAME ( DDAYSDATE ) ;
            SET CDAY3CHAR = SUBSTR ( VDAYNAME , 1 , 3 ) ;
            SET VMONNAME = MONTHNAME ( DDAYSDATE ) ;
            SET CMON3CHAR = SUBSTR ( VMONNAME , 1 , 3 ) ;
            SET VYYYY_MM_DD = CYYYY || '-' || CMM || '-' || CDD ;
            IF IMM > 3 THEN SET IFISCALYYYY = IYYYY ;
                  ELSE SET IFISCALYYYY = IYYYY - 1 ;
                  END IF ;
            INSERT INTO MYLIBNAME . YMD (
                  DAYDATE , YYYY , MM , MON3CHAR , MONNAME , DAY3CHAR , DAYNAME ,
                  YY2CHAR , MM2CHAR , DD2CHAR , YYYYMMDD , YYYY_MM_DD , FISCALYYYY , DD )
            VALUES (
                  DDAYSDATE , IYYYY , IMM , CMON3CHAR , VMONNAME , CDAY3CHAR , VDAYNAME ,
                  CYY , CMM , CDD , IIYMD , VYYYY_MM_DD , IFISCALYYYY , IDD ) ;
            SET DDAYSDATE = DDAYSDATE + EDAY1 ;
      END WHILE ;
END P1  ;
------------------------------------------------------------------------
COMMENT ON SPECIFIC PROCEDURE MYLIBNAME.YMD_BUILD
      IS 'Builds YMD table for 01/01/1950 -to- 01/01/2051' ;
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
volkingAuthor Commented:
Create a User Defined Function UDF
This one uses the YMD table above to convert an Integer value like 20080105 into a date of 01/05/2008

CREATE FUNCTION MYLIBNAME.YMD2DATE (
      PARAM1 INTEGER )
      RETURNS DATE  
      LANGUAGE SQL
      SPECIFIC MYLIBNAME.YMD2DATE
      NOT DETERMINISTIC
      READS SQL DATA
      CALLED ON NULL INPUT
      DISALLOW PARALLEL
      BEGIN
      DECLARE RET DATE ;
      SELECT DAYDATE
            INTO RET
            FROM MYLIBNAME . YMD WHERE MYLIBNAME . YMD . YYYYMMDD = PARAM1 ;
      RETURN RET ;
      END  ;
------------------------------------------------------------------------
COMMENT ON SPECIFIC FUNCTION MYLIBNAME.YMD2DATE
      IS 'Returns DATE for integer of YYYYMMDD' ;
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Fred,

The DB2 online documentation has examples of most every supported SQL statement.    :)

DB2/LUW here:  http://publib.boulder.ibm.com/infocenter/db2luw/v9//index.jsp

AS/400 here:     http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rbafymst02.htm



Kent
0
 
volkingAuthor Commented:
And here's one more function ... not related to the above YMD stuff
It takes a VARCHAR like 'R,B,XYZ,091' (often called a CSV or Comma Seperated Value String)
and returns a TABLE like
R
B
XYZ
091

The function can be used for "IN" conditions like this
SELECT * FROM MyTable WHERE MyCODE IN (SELECT ITM FROM MYLIBNAME.CSV2VCHAR('A,B,C,XYZ,ABC'))

CREATE FUNCTION MYLIBNAME.CSV2VCHAR (
      CSV VARCHAR(1000) )
      RETURNS TABLE (
      ITM VARCHAR(50) )  
      LANGUAGE SQL
      SPECIFIC MYLIBNAME.CSV2VCHAR
      DETERMINISTIC
      MODIFIES SQL DATA
      CALLED ON NULL INPUT
      DISALLOW PARALLEL
      BEGIN
      DECLARE ITEM VARCHAR ( 100 ) ;
      DECLARE POS INTEGER DEFAULT 1 ;
      DECLARE NEXTPOS INTEGER DEFAULT 0 ;
      DECLARE LENINPUT INTEGER DEFAULT 0 ;
      DECLARE LENNEXT INTEGER DEFAULT 0 ;
      SET LENINPUT = CHARACTER_LENGTH ( CSV ) ;
      SET NEXTPOS = LOCATE ( ',' , CSV , 1 ) ;
      DECLARE GLOBAL TEMPORARY TABLE ITEMS ( ITM VARCHAR ( 50 ) ) WITH REPLACE NOT LOGGED ;
      WHILE ( ( POS <= LENINPUT ) AND ( NEXTPOS > 0 ) ) DO
            IF NEXTPOS > POS THEN
                  SET LENNEXT = NEXTPOS - POS ;
                  SET ITEM = TRIM ( SUBSTR ( CSV , POS , LENNEXT ) ) ;
                  IF CHARACTER_LENGTH ( ITEM ) > 0 THEN
                        INSERT INTO SESSION . ITEMS ( ITM ) VALUES ( ITEM ) ;
                  END IF ;
            END IF ;
            SET POS = NEXTPOS + 1 ;
            SET NEXTPOS = LOCATE ( ',' , CSV , POS ) ;
            IF NEXTPOS > 0 THEN
                  SET NEXTPOS = ( POS + NEXTPOS ) - 1 ;
            END IF ;
      END WHILE ;
      SET ITEM = TRIM ( SUBSTR ( CSV , POS , LENINPUT - POS + 1 ) ) ;
      IF CHARACTER_LENGTH ( ITEM ) > 0 THEN
            INSERT INTO SESSION . ITEMS ( ITM ) VALUES ( ITEM ) ;
      END IF ;
      RETURN SELECT ITM FROM SESSION . ITEMS ;
END  ;
------------------------------------------------------------------------  
COMMENT ON SPECIFIC FUNCTION MYLIBNAME.CSV2VCHAR
      IS 'Converts CSV string into TABLE of ITEMS' ;
0
 
volkingAuthor Commented:
Of course, the ONLINE IBM Documentation is a great source too.
0
 
volkingAuthor Commented:
@Kent ...
Google this
DB2 "DECLARE GLOBAL TEMPORARY TABLE" "WITH REPLACE" "NOT LOGGED"

Only 84 hits ... and NONE have actual source code for SQL DB2 AS400 iSeries R5V3M0

After Google gets refreshed, there will now be 85 hits, and one will actually have code!

Grin ... later
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now