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
CREATE OR Replace PROCEDURE Test.Load_Copy_Table (
       IN SERVER CHAR(8)
      ,IN REMOTE_TABLE VARCHAR(128)
      ,IN LOCAL_TABLE  VARCHAR(128)
      ,IN LOAD_ART     VARCHAR(128)
      ,OUT ReturnCode  INTEGER
     )
 RESULT SETS 1
 LANGUAGE SQL

BEGIN
 DECLARE SQLCODE 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;

 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 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 = '';

EXECUTE IMMEDIATE 'commit';

Call DBMS_OUTPUT.PUT_LINE('DECLARE C1 CURSOR FOR SELECT * FROM ' CONCAT From_Table   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 ' WITH UR '
   , 'load from C1 of cursor '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);

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 sqlcode < 0 Then
   SET retcode = 8;
   SET MESSAGETEXT = CHAR(Sqlcode) CONCAT 'Fehler beim Aufruf  sysproc.db2load';
   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';


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;
  -- set SQLTEXT = 'SET SERVEROUTPUT OFF ' ;
  -- EXECUTE IMMEDIATE SQLTEXT;
END
#