Solved

Samples SQL DB2 AS400 iSeries R5V3M0

Posted on 2008-09-30
8
1,609 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
  • 7
8 Comments
 
LVL 5

Author Comment

by:volking
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Author Comment

by:volking
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 45

Accepted Solution

by:
Kdo earned 20 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Of course, the ONLINE IBM Documentation is a great source too.
0
 
LVL 5

Author Comment

by:volking
Comment Utility
@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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL0338 Error received on query 3 284
Wrong record extracting - DB2 SQL 10 155
SELECT * FROM [Stored Procedure] 6 77
DB2 return first match 3 90
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now