DB2 z/OS   DB2 LUW
z/OS CICS VTAM

DB2 LUW Functions

Home

String Parser 

CREATE FUNCTION DB2ADMIN.STR_PARSE (
    "SSTRING"    VARCHAR(4000),
    "SARG"    CHARACTER(1),
    "SNR"    INTEGER )
  RETURNS VARCHAR(4000)
  SPECIFIC STR_PARSE_VARCHAR
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  CONTAINS SQL
  INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
    DECLARE SPOS INT ;
    DECLARE ARGNR INT ;
    DECLARE STRING CHAR(100) ;
    DECLARE FIND INT;
    DECLARE SUCHSTR varchar(4000);
   
    SET SPOS  = 1 ;
    SET FIND  = 1 ;
    SET STRING = '?' ;
    SET ARGNR  = 1 ;
    SET SUCHSTR = SSTRING;
   
    WHILE(FIND > 0 or ARGNR >= SNR ) Do
          SET FIND    = LOCATE(SARG, SUCHSTR, SPOS) ;
          If Find > 0 Then 
             SET STRING  = SUBSTR(SUCHSTR, 1, FIND - 1) ;
             SET SUCHSTR = RTrim(SUBSTR(SUCHSTR, FIND + 1)) ;
          ELSE
             SET STRING  = SUCHSTR ;
             SET SUCHSTR = '' ;
             If Argnr <> SNR Then Return '?' ;
             ELSE Return STRING ;
             End if;
           End If ;
          
          If ARGNR = SNR  Then
             Return STRING ;
           End If ;
          
          SET ARGNR  = ARGNR + 1 ;
    END while;
   
    SET STRING = '?' ;
    RETURN STRING ;   
  END

#

With TAB (DSN) AS
     (
     Select 'eins.zwei.drei.vier.fuenf'  from sysibm.sysdummy1 union all
     Select 'drei.zwei.eins.vier.sechs' from sysibm.sysdummy1
     )
Select dsn from tab
Where DB2ADMIN.STR_PARSE(dsn, '.', 5) = 'fuenf'

#

Ostersonntag berechnen 

CREATE FUNCTION "DB2ADMIN"."OSTERN"
 ("CHECKDATE" DATE )
  RETURNS DATE
  SPECIFIC "DB2ADMIN"."OSTERN"
  LANGUAGE SQL
  DETERMINISTIC
  CONTAINS SQL
  STATIC DISPATCH
  CALLED ON NULL INPUT
  NO EXTERNAL ACTION
  INHERIT SPECIAL REGISTERS
  BEGIN ATOMIC
    DECLARE OSTERN DATE ;
    DECLARE A INT ;
    DECLARE B INT ;
    DECLARE C INT ;
    DECLARE D INT ;
    DECLARE E INT ;
    DECLARE M INT ;
    DECLARE N INT ;
    DECLARE S INT ;
    DECLARE JAHR CHAR(4);
    SET A = MOD(Year(CHECKDATE) , 19) ;
    SET B = MOD(Year(CHECKDATE) , 4) ;
    SET C = MOD(Year(CHECKDATE) , 7) ;
    SET M = INT(( 8 * ( Year(CHECKDATE) / 100) + 13) / 25 - 2) ;
    SET S = INT ((Year(CHECKDATE) / 100) - (Year(CHECKDATE) / 400) - 2) ;
    SET M = MOD((15 + s - m) , 30 ) ;
    SET N = MOD(( 6 + s) , 7) ;
    SET D = case    when MOD((M + 19 * A) , 30) = 29                         Then 28
                              when MOD((M + 19 * A) , 30) = 28   And A >= 11   Then 27
                     Else MOD((M + 19 * A) , 30)
                    END ;
    SET E = MOD((2 * b + 4 * c + 6 * D + N) , 7) ;
    SET JAHR = char(Substr(DIGITS(year(CHECKDATE)), 7, 4), 4) ;
    SET OSTERN = Date('21.03.' concat JAHR) + (D + e + 1) Days ;
    RETURN OSTERN ;
  END
#


Crossload als Stored Procedure

-- db2 SET SERVEROUTPUT ON
-- copy reote tabllen (nicknames)
-- db2 call Test.Load_Copy_Table('<server>','test.TEST01','test.test02','R',  ?)
-- fuer lokale Tabellen
-- db2 call Test.Load_Copy_Table('','test.TEST01','test.test02','R',  ?)
-- db2 SET SERVEROUTPUT OFF

--<ScriptOptions statementTerminator = ";"/>

Set Current schema = 'TEST'  ;

--<ScriptOptions statementTerminator = "#"/>

CREATE or replace PROCEDURE LOAD_COPY_TABLE (
    IN SERVER   CHARACTER(8),
    IN REMOTE_TABLE VARCHAR(128),
    IN LOCAL_TABLE  VARCHAR(128),
    IN LOAD_ART VARCHAR(128),
    IN WCLAUSE  VARCHAR(10000),
    OUT RETURNCODE  INTEGER )
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  EXTERNAL ACTION
  MODIFIES SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
  OLD SAVEPOINT LEVEL
BEGIN

 DECLARE SQLCODE                  INTEGER DEFAULT 0;
 DECLARE SQLCODE_SAVE             INTEGER DEFAULT 0;
 DECLARE SQLSTATE                 CHAR(5) DEFAULT '';
 DECLARE retcode                  INTEGER DEFAULT 0;
 DECLARE DotPos                   INTEGER DEFAULT 0;
 DECLARE R_Creator                VARCHAR(128) DEFAULT '';
 DECLARE R_Table                  VARCHAR(128) DEFAULT '';
 DECLARE L_Creator                VARCHAR(128) DEFAULT '';
 DECLARE L_Table                  VARCHAR(128) DEFAULT '';
 DECLARE Load_Type                VARCHAR(30) DEFAULT 'REPLACE' ;
 DECLARE From_Table               VARCHAR(128) DEFAULT '';
 DECLARE To_Table                 VARCHAR(128) DEFAULT '';
 DECLARE MESSAGETEXT              VARCHAR(32000) DEFAULT '';
 DECLARE SQLTEXT                  VARCHAR(32000) DEFAULT '';
 DECLARE L_ZEichen                CHAR(1) DEFAULT ' ';
 DECLARE TabelleDa                INTEGER DEFAULT 0;
 DECLARE ServerDa                 INTEGER DEFAULT 0;
 DECLARE Start                    TIMESTAMP;
 DECLARE Ende                     TIMESTAMP;
 DECLARE v_version_number         INTEGER DEFAULT 1;
 DECLARE v_cursor_statement       VARCHAR(32672);
 DECLARE v_load_command           VARCHAR(32672);
 DECLARE v_sqlcode                INTEGER DEFAULT - 1;
 DECLARE v_sqlmessage             VARCHAR(2048) DEFAULT '';
 DECLARE v_rows_read              BIGINT DEFAULT - 1;
 DECLARE v_rows_skipped           BIGINT DEFAULT - 1;
 DECLARE v_rows_loaded            BIGINT DEFAULT - 1;
 DECLARE v_rows_rejected          BIGINT DEFAULT - 1;
 DECLARE v_rows_deleted           BIGINT DEFAULT - 1;
 DECLARE v_rows_committed         BIGINT DEFAULT - 1;
 DECLARE v_rows_part_read         BIGINT DEFAULT - 1;
 DECLARE v_rows_part_rejected     BIGINT DEFAULT - 1;
 DECLARE v_rows_part_partitioned  BIGINT DEFAULT - 1;
 DECLARE v_mpp_load_summary       VARCHAR(32672) DEFAULT NULL;
 DECLARE V_WHERE_STMT             VARCHAR(10000) DEFAULT ''   ;
 DECLARE V_IDENTITY               VARCHAR(100) DEFAULT ''   ;
 DECLARE V_IDENTITY_COLUMN        VARCHAR(128) DEFAULT ''   ;
 DECLARE V_IDENTITY_MAX_VALUE     BIGINT DEFAULT 0;
 
 Declare C1 Cursor for SelectStatement;
 
 If WCLAUSE <> '' AND WCLAUSE IS NOT NULL Then
    Set V_WHERE_STMT =  WCLAUSE ;
  End IF ;
 
 SET SQLTEXT = 'SET SERVEROUTPUT ON ';
 -- EXECUTE IMMEDIATE SQLTEXT;

 If Upper(Load_Art) = 'I' Then
    SET Load_Type = 'INSERT' ;
  END IF ;
 SET DotPos = LOCATE('.', REMOTE_TABLE);
 IF DotPos > 0 Then
    SET R_Creator = Substr(REMOTE_TABLE, 1, Dotpos - 1);
    SET R_Table = Substr(REMOTE_TABLE, Dotpos + 1);
ELSE
    SET retcode = 8;
    SET MESSAGETEXT =   'Remote Table muss Qualifiziert angegeben werden Creator.Name '  ;
    Call DBMS_OUTPUT.PUT_LINE(MESSAGETEXT);
    GOTO ERROR;
END IF ;

SET DotPos = LOCATE('.', LOCAL_TABLE);
IF DotPos > 0 Then
   SET L_Creator = Substr(LOCAL_TABLE, 1, Dotpos - 1);
   SET L_Table = Substr(LOCAL_TABLE, Dotpos + 1);
ELSE
   SET retcode = 8;
   SET MESSAGETEXT =  'Local Table muss Qualifiziert angegeben werden Creator.Name ' ;
   Call DBMS_OUTPUT.PUT_LINE(MESSAGETEXT);
   GOTO ERROR;
 END IF ;

SET To_Table    = L_Creator CONCAT '.' CONCAT L_Table;
SET MESSAGETEXT = 'Local  Table ' CONCAT L_Creator CONCAT '.' CONCAT  L_Table;
Call DBMS_OUTPUT.PUT_LINE(MESSAGETEXT);

SET MESSAGETEXT = 'Remote Table ' CONCAT RTrim(SERVER) Concat CASE When Server = '' Then '' ELSE '.' END  
                   CONCAT R_Creator CONCAT '.' CONCAT   R_Table
                   ;
                  
Call DBMS_OUTPUT.PUT_LINE(MESSAGETEXT);

IF SERVER = '' Then
   SET From_Table = R_Creator CONCAT '.' CONCAT R_Table;
   SET TabelleDa = 0;

   SELECT COUNT(*)
   INTO TabelleDa
   FROM syscat.tables
   WHERE Upper(TABSCHEMA) = Upper(R_Creator)
     AND Upper(TABNAME) = Upper(R_Table);

   IF TabelleDa = 0 Then
      SET retcode = 8;
      SET MESSAGETEXT = 'From Table Nicht in Syscat.Tables Creator.Name  ' CONCAT From_Table;
      GOTO ERROR;
      END IF ;
ELSE
   SET ServerDa = 0;
   SELECT COUNT(*) INTO ServerDa
   FROM syscat.SERVERS
   WHERE Upper(SERVERNAME) = Upper(SERVER);

   IF ServerDa > 0 Then
      SET From_Table = RTrim(SERVER) CONCAT '.' CONCAT R_Table;
      SET SQLTEXT = 'CREATE OR REPLACE NICKNAME  '
                        CONCAT  RTrim(SERVER) CONCAT '.' CONCAT RTrim(R_Table)
                        CONCAT ' FOR '
                        CONCAT RTrim( SERVER) CONCAT '.' CONCAT RTrim(R_Creator) CONCAT '.' CONCAT RTrim(R_Table);
      EXECUTE IMMEDIATE SQLTEXT;

      Call DBMS_OUTPUT.PUT_LINE(CHAR(Sqlcode) CONCAT SQLTEXT);
      SET SQLTEXT = '';
      IF sqlcode < 0 Then
         SET retcode = 8;
         SET MESSAGETEXT = CHAR(Sqlcode) CONCAT 'Remote Table existiert nicht ' CONCAT SQLTEXT;
         GOTO ERROR;
      END IF ;
    ELSE
       SET retcode = 8;
       SET MESSAGETEXT = 'Server nicht in Syscat.Servers ' CONCAT SERVER ;
       GOTO ERROR;
     END IF ;
  END IF ;

SET TabelleDa = 0;
SELECT COUNT(*) INTO TabelleDa
FROM syscat.tables
WHERE Upper(TABSCHEMA) = Upper(L_Creator)
  AND Upper(TABNAME)   = Upper(L_Table);

IF TabelleDa = 0 Then
   SET retcode = 8;
   SET MESSAGETEXT = 'TO  Table Nicht in Syscat.Tables Creator.Name '
                      CONCAT  L_Creator CONCAT '.' CONCAT L_Table CONCAT ' ';
   GOTO ERROR;
END IF ;
SET MESSAGETEXT = '';

SELECT CASE WHEN EXISTS (SELECT 1 FROM SYSCAT.COLUMNS CO
                                         WHERE UPPER(CO.TABSCHEMA) = Upper(L_Creator)
                                         AND   UPPER(CO.TABNAME)   = Upper(L_Table)
                                         AND   IDENTITY = 'Y'
                                         AND   GENERATED = 'A' )
                                         THEN ' MODIFIED BY IDENTITYOVERRIDE '
                            WHEN EXISTS (SELECT 1 FROM SYSCAT.COLUMNS CO
                                         WHERE UPPER(CO.TABSCHEMA) = Upper(L_Creator)
                                         AND   UPPER(CO.TABNAME)   = Upper(L_Table)
                                         AND IDENTITY <> 'Y'
                                         AND GENERATED = 'A' )
                                         THEN        ' MODIFIED BY GENERATEDOVERRIDE  '
                            ELSE '' END
                    INTO V_IDENTITY
                    FROM SYSIBM.SYSDUMMY1
                               
;           


Call DBMS_OUTPUT.PUT_LINE('SQLCODE/SQLSTATE 11    ' Concat CHAR(Sqlcode) CONCAT '/ 'Concat SQLSTATE CONCAT ' ' CONCAT V_IDENTITY);

EXECUTE IMMEDIATE 'commit' ;

Call DBMS_OUTPUT.PUT_LINE('Load_Copy_Table V3.1 ') ;
Call DBMS_OUTPUT.PUT_LINE('DECLARE C1 CURSOR FOR SELECT * FROM ' CONCAT From_Table concat ' ' concat V_WHERE_STMT CONCAT ' with ur ') ;
Call DBMS_OUTPUT.PUT_LINE('LOAD FROM C1 OF CURSOR 'Concat Load_Type concat ' INTO ' CONCAT To_Table CONCAT ' NONRECOVERABLE ');

SET Start = CURRENT TIMESTAMP;

CALL sysproc.db2load(v_version_number
   , 'declare C1 cursor for SELECT * from ' CONCAT From_Table concat ' ' concat V_WHERE_STMT  CONCAT ' WITH UR '
   , 'load from C1 of cursor ' concat V_IDENTITY Concat Load_Type concat' into '  CONCAT To_Table CONCAT ' NONRECOVERABLE '
   , v_sqlcode
   , v_sqlmessage
   , v_rows_read
   , v_rows_skipped
   , v_rows_loaded
   , v_rows_rejected
   , v_rows_deleted
   , v_rows_committed
   , v_rows_part_read
   , v_rows_part_rejected
   , v_rows_part_partitioned
   , v_mpp_load_summary);

IF sqlcode < 0 Then
   SET retcode = 8;
   SET MESSAGETEXT = CHAR(Sqlcode) CONCAT 'Fehler beim Aufruf  sysproc.db2load';
   SET SQLCODE_SAVE = SQLCODE;
 END IF ;
  

SET Ende = CURRENT TIMESTAMP;

Call DBMS_OUTPUT.PUT_LINE('SQLCODE/SQLSTATE      ' Concat CHAR(Sqlcode) CONCAT '/ 'Concat SQLSTATE CONCAT ' ' CONCAT v_sqlmessage);
Call DBMS_OUTPUT.PUT_LINE('Rows Read             ' CONCAT ' '   CONCAT CHAR(v_rows_read));
Call DBMS_OUTPUT.PUT_LINE('Rows Skipped          ' CONCAT ' '   CONCAT CHAR(v_rows_skipped));
Call DBMS_OUTPUT.PUT_LINE('Rows loaded           ' CONCAT ' '   CONCAT CHAR(v_rows_loaded));
Call DBMS_OUTPUT.PUT_LINE('Rows rejected         ' CONCAT ' '   CONCAT CHAR(v_rows_rejected));
Call DBMS_OUTPUT.PUT_LINE('Rows Deleted          ' CONCAT ' '   CONCAT CHAR(v_rows_deleted));
Call DBMS_OUTPUT.PUT_LINE('Rows Commited         ' CONCAT ' '   CONCAT CHAR(v_rows_committed));
Call DBMS_OUTPUT.PUT_LINE('Rows PartRead         ' CONCAT ' '   CONCAT CHAR(v_rows_part_read));
Call DBMS_OUTPUT.PUT_LINE('Rows PartRejected     ' CONCAT ' '   CONCAT CHAR(v_rows_part_rejected));
Call DBMS_OUTPUT.PUT_LINE('Rows partPartitioned  ' CONCAT ' '   CONCAT CHAR(v_rows_part_partitioned));
Call DBMS_OUTPUT.PUT_LINE('Rows LoadSummary      ' CONCAT ' '   CONCAT CHAR(v_rows_committed));
Call DBMS_OUTPUT.PUT_LINE('Laufzeit Von          ' CONCAT CHAR  (Start) CONCAT ' - ' CONCAT CHAR(Ende)) ;
Call DBMS_OUTPUT.PUT_LINE('Laufzeit              '
concat  Char( TIME(
               CHAR(DIGITS(DECIMAL(TIMESTAMPDIFF(8, CHAR(Ende - Start)), 2, 0))) CONCAT ':'
        CONCAT CHAR(DIGITS(DECIMAL(MOD(TIMESTAMPDIFF(4, CHAR(Ende - Start)), 60), 2, 0))) CONCAT ':'
        CONCAT CHAR(DIGITS(DECIMAL(MOD(TIMESTAMPDIFF(2, CHAR(Ende - Start)), 60), 2, 0))) )
        ))
;

IF retcode = 8 Then
   SET SQLCODE = SQLCODE_SAVE;
   GOTO ERROR;
 END IF ;

SET MESSAGETEXT = '';

IF SERVER <> '' Then
  SET SQLTEXT = 'DROP NICKNAME  ' CONCAT RTrim(SERVER) CONCAT '.'  CONCAT RTrim(R_Table);
  EXECUTE IMMEDIATE SQLTEXT;
  Call DBMS_OUTPUT.PUT_LINE(CHAR(Sqlcode) CONCAT SQLTEXT);
  SET SQLTEXT = '';
END IF ;
EXECUTE IMMEDIATE 'commit';

IF LOCATE('IDENTITYOVERRIDE', UCASE(V_IDENTITY), 1) > 0 Then
   SET V_IDENTITY_COLUMN = (SELECT COLNAME
                            FROM SYSCAT.COLUMNS CO
                            WHERE CO.TABSCHEMA = L_CREATOR
                            AND   CO.TABNAME   = L_TABLE
                            AND   IDENTITY     = 'Y'
                            AND   GENERATED    = 'A' )
                            ;


   SET SQLTEXT     = 'SELECT MAX(' CONCAT V_IDENTITY_COLUMN concat ') + 1 '
            concat ' FROM ' Concat To_Table
                            ;       


      Prepare SelectStatement    from SQLTEXT ;
    Open C1 ;
    Fetch C1 INTO V_IDENTITY_MAX_VALUE ;
    Close C1 ;                           

    
   SET SQLTEXT = 'ALTER TABLE  ' CONCAT To_Table
                 CONCAT  ' ALTER COLUMN ' CONCAT RTRIM(V_IDENTITY_COLUMN)
                 CONCAT  ' RESTART WITH '
                 CONCAT CAST(V_IDENTITY_MAX_VALUE AS CHAR(25)  )
           ;
          
   EXECUTE IMMEDIATE SQLTEXT;
  

   Call DBMS_OUTPUT.PUT_LINE(RTrim(SQLTEXT) CONCAT ' ' CONCAT  CHAR(Sqlcode) CONCAT '/ 'Concat SQLSTATE CONCAT ' ' );
                           
END IF ;

EXECUTE IMMEDIATE 'commit';

ERROR: BEGIN
 IF retcode > 0 THEN
    Call DBMS_OUTPUT.PUT_LINE(CHAR(Sqlcode) CONCAT MESSAGETEXT CONCAT CHAR(Retcode));
    SIGNAL SQLSTATE '70000'
    SET MESSAGE_TEXT = MESSAGETEXT;
    SET ReturnCode = retcode;
  END IF ;

END  ERROR;

 END

#