Solved

Samples SQL DB2 AS400 iSeries R5V3M0

Posted on 2008-09-30
8
1,631 Views
Last Modified: 2010-04-21
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
Comment
Question by:volking
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
8 Comments
 
LVL 5

Author Comment

by:volking
ID: 22608549
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
 
LVL 5

Author Comment

by:volking
ID: 22608557
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
 
LVL 5

Author Comment

by:volking
ID: 22608569
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 5

Author Comment

by:volking
ID: 22608587
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
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 20 total points
ID: 22608628
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
 
LVL 5

Author Comment

by:volking
ID: 22608629
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
 
LVL 5

Author Closing Comment

by:volking
ID: 31501699
Of course, the ONLINE IBM Documentation is a great source too.
0
 
LVL 5

Author Comment

by:volking
ID: 22608716
@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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Procedure to create a text file in DB2 on AS400 15 506
eclipse find/step into stored procedure 3 200
compare column value for each row 4 82
CATALOG A DB2 TCPIP NODE with AN ALIAS 2 143
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question