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
#