DB2 z/OS DB2 LUW
z/OS CICS VTAM

DB2 LUW

Catalog DCS Database :

HOSTIP = IP-Adresse des Zielrechners
HOSTPORT = PORT DES HOST DB2's
HOSTLocation = Locationname des ziel DB2's

db2 CATALOG TCPIP NODE TCPZOS01 REMOTE <HOSTIP> SERVER <HOSTPORT>
db2 CATALOG DCS DATABASE DCSDB2A AS <HOSTLocation> AR <HOSTLocation>
db2 Catalog Database DCSDB2A as <DB2A> AT NODE TCPZOS01

Datum EUR (tt.mm.jjjj) :

WIN : cd "C:\Program Files (x86)\IBM\SQLLIB\bnd" UNIX :

chdir
/home/db2inst1/sqllib/bnd

db2 connect to DBNAME
db2 bind @db2ubind.lst datetime EUR blocking all grant public

DB2CMD

db2cmd /i /c /w db2 -tvf filename.sq

SQL aus Notepad++ oder Notepad++Portable ausführen (Ultraedit oder .. geht auch)

In Notepad++ (F5 oder ausführen und danach folgendes eingeben)

die Batchdatei steht unter c:\temp ausgabe geht nach c:\temp\sqlout

db2cmd C:\Temp\db2run.bat $(FULL_CURRENT_PATH) c:\temp\sql.out

die db2run.Bat sieht so aus !

db2 -F %1 -O -T -S -V -z %2
D:\Portable_APPS_Plus\PortableApps\Notepad++Portable\Notepad++Portable.exe %2
exit

Configuration Löschen

db2 UPDATE DBM CFG USING SYSADM_GROUP NULL DEFERRED

Configuration ändern

db2 update dbm config using SYSADM_GROUP DB2ADMNS

Meine Standard Konfiguration

DBM CFG

rem db2 UPDATE DBM CFG USING SYSADM_GROUP NULL DEFERRED
db2 update dbm config using SYSADM_GROUP DB2ADMNS
db2 update dbm config using SYSCTRL_GROUP DB2ADMNS
db2 update dbm config using SYSMAINT_GROUP DB2ADMNS
db2 update dbm config using SYSMON_GROUP DB2ADMNS

db2 update dbm config using NUMDB 10
db2 update dbm config using FEDERATED YES
db2 update dbm config using DFTDBPATH D:\Databases
db2 update dbm config using SVCENAME 50000

db2set DB2_CREATE_DB_ON_PATH=YES
db2set DB2_EXTSECURITY=NO
db2set DB2_GRP_LOOKUP=LOCAL,TOKENLOCAL

db2stop force
db2start

DB CFG

echo "Eingabe UpdateDBCFG.Bat <Datenbank>"
if %1 =='' goto ende
db2 connect to %1
db2 GRANT DBADM ON DATABASE TO GROUP DB2ADMNS
db2 GRANT SECADM ON DATABASE TO GROUP DB2ADMNS
db2 connect reset

db2 UPDATE DATABASE CONFIGURATION FOR %1 USING logarchmeth1 'LOGRETAIN'
db2 UPDATE DATABASE CONFIGURATION FOR %1 USING num_db_backups 5
db2 UPDATE DATABASE CONFIGURATION FOR %1 USING trackmod Yes

:Ende

DB2Look

-- ddl
db2look -d <dbname> -e -i userid -w pwd -z <creator> -t <name> -ct
oder
db2look -d <dbname> -e -l -f -printdbcfg -createdb -i userid -w pwd -z <creator> -t <name> -ct

db2look -d <dbname> -e -i userid -w pwd -z <creator> -tw <nam%> -ct

-- Statistics
db2look -d <dbname> -m -i userid -w pwd -z <creator> -t <name> -ct

DB2Look mit SProc DB2LK_GENERATE_DDL

db2 CALL SYSPROC.DB2LK_GENERATE_DDL('-e -z "Creator"', ?)
db2 CALL SYSPROC.DB2LK_GENERATE_DDL('-e -z "SYSIBM" -TW "SYSTABLE%" ', ?)
db2 select * from SYSTOOLS.DB2LOOK_INFO

DOS

Verarbeitung wenn eine datei da ist

@ECHO off
IF NOT EXIST D:\TEST\datei.ixf goto :NICHTDA

@ECHO Alles paletti

DB2 IMPORT from D:\TEST\datei.ixf of ixf ....
goto :ENDE

:NICHTDA
@ECHO war wohl nix

:ENDE

Verarbeitung wenn ein Satz in einer Tabelle gefunden wurde !

DB2 Connect to <Datenbank> user ... using ...

DB2 "SELECT NAME FROM SYSIBM.SYSTABLES WHERE NAME LIKE 'TEST%' FETCH FIRST 1 ROWS ONLY"
IF %errorlevel% == 0 GOTO TABELLE_Gefunden

GOTO NIGEFU

:TABELLE_Gefunden

Rem Die Tabelle wurde gefunden und kann verarbeitet werden !

db2 "Import from ..... "

GOTO ENDE

:NIGEFU
REM Tabelle nicht gefunden dann Fehlermeldung ausgeben
REM oder ARLARM schlagen

GOTO ENDE

:ENDE

db2 connect reset

Binden

db2 force applications all
db2 connect to <datenbank>
db2 bind @db2ubind.lst blocking all grant public
db2 bind @db2cli.lst blocking all grant public
db2 connect reset

Event Monitor

set current schema db2admin ;

DROP TABLE CONNHEADER_EVM_STMT;
DROP TABLE CONTROL_EVM_STMT;
DROP TABLE XACT_EVM_STMT;
DROP TABLE STMT_EVM_STMT;

drop event monitor EVM_STMT
;

-- BEIM ERSTEN MAL AB HIER LAUFEN LASSEN

create event monitor EVM_STMT for
statements ,
TRANSACTIONS
write to TABLE
;

-- AKTIVATE
set event monitor EVM_STMT STATE 1 ;

-- Dein SQL Statement
Select * from sysibm.systables ;
Commit ;

-- DEACTIVATE

set event monitor EVM_STMT STATE 0 ;
Commit ;

-- Da stehen die Werte drinnen
select * from CONTROL_EVM_STMT;
select * from STMT_EVM_STMT;

Tabellen Ändern mit SYSPROC.ALTOBJ

CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR',
                      'CREATE TABLE XXX.XXX (
TEST CHARACTER (1)
                                                , TEST99 CHARACTER (1)
                                                , TEST2 BIGINT
                                                , TEST3 CHARACTER (1)
                                                , TEST5 BIGINT NOT NULL
                                                , TEST4 BIGINT NOT NULL
                                             ) IN USERSPACE1
                                               COMPRESS YES '
                       , -1, ? );

SQL Ohne LOGGING ausführen

Aufpassen keine LOGGS heißt kein recover

ALTER TABLE Creator.Name ACTIVATE NOT LOGGED INITIALLY;
Update ...
Insert ...
Commit ;

statement termination characte in a script

--#SET TERMINATOR #

connect to database #
connect reset #

SQL Generieren und ausführen :

Script sqlgen :

Echo Aufruf sqlgen.bat datenbank sqlscript (gen)
del %2.sql
db2 connect to %1
db2 -x -tf %2.gen -z %2.sql
db2 connect reset

db2 -tvf %2.sql

Script PruneLogfiles.gen :

SELECT 'CONNECT TO ' CONCAT CURRENT SERVER CONCAT ' ;'
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 'PRUNE LOGFILE PRIOR TO '
CONCAT CHAR(FIRSTLOG,15)
CONCAT ' ;'
FROM ( SELECT FIRSTLOG, START_TIME
       FROM SYSIBMADM.DB_HISTORY
       WHERE OPERATION          = 'B'
       AND   OPERATIONTYPE    IN ('F', 'N')
       ORDER BY START_TIME
       FETCH FIRST 1 ROWS ONLY
     ) T1
UNION ALL
SELECT 'CONNECT RESET ; ' FROM SYSIBM.SYSDUMMY1
FOR FETCH ONLY     WITH UR

Aufruf :

sqlgen sample PruneLogFiles

Create Database (So mache ich es als batch)

db2 create database %1 AUTOMATIC STORAGE YES ON d:\Databases\%1 DBPATH ON d:\Databases\%1

Cygwin und DB2 :

ins Profile habe ich folgendes eingetragen :

export PATH=$PATH:.:
export DB2CLP=**$$**
export DB2INSTANCE=DB2
export DB2PATH='C:\Program Files (x86)\IBM\SQLLIB'
export PATH=$PATH'/cygdrive/c/PROGRA~2/IBM/SQLLIB/db2tss/bin:/cygdrive/c/Program Files (x86)/IBM/SQLLIB/BIN:/cygdrive/c/Program Files (x86)/IBM/SQLLIB/FUNCTION:'

danach neu starten oder . .profile
dann db2-tvf xxx.sql
und es klappt

Locks provozieren :

Session 1

db2 create table xxx.xxx (COL1 char(10) )
db2 insert into xxx.xxx values('HALLO 1')
db2 +c update xxx.xxx set col1 = 'HALLO 2'

Session 2

db2 SET LOCK TIMEOUT=WAIT 900
db2 drop table xxx.xxx

Session 3 Prüfen Lockwaits + Lockholder :

db2 select * from FROM SYSIBMADM.MON_LOCKWAITS for fetch only with ur

TABLESAMPLE :

Select name from sysibm.systables tb tablesample BERNOULLI (10)
;

RUNSTATS ON TABLE SYSIBM.SYSTABOPTIONS ON ALL COLUMNS
WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEX ALL
ALLOW WRITE ACCESS
TABLESAMPLE BERNOULLI (10)
;

RUNSTATS ON TABLE SYSIBM.SYSTABOPTIONS ON ALL COLUMNS
WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEX ALL
ALLOW WRITE ACCESS
TABLESAMPLE SYSTEM (10)
;

Auto Maint :

Runstats :

CONNECT TO <DATABASE>;
CALL SYSPROC.AUTOMAINT_SET_POLICY
     ('MAINTENANCE_WINDOW', BLOB('<?xml version="1.0" encoding="UTF-8"?><DB2MaintenanceWindows xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"><OnlineWindow Occurrence="During" startTime="22:00:00" duration="5"><DaysOfWeek> Mon Fri Sat Sun</DaysOfWeek><DaysOfMonth>All</DaysOfMonth><MonthsOfYear> All</MonthsOfYear></OnlineWindow></DB2MaintenanceWindows> ') );
CALL SYSPROC.AUTOMAINT_SET_POLICY
     ( 'AUTO_RUNSTATS', BLOB('<?xml version="1.0" encoding="UTF-8"?><DB2AutoRunstatsPolicy xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config"><RunstatsTableScope ><FilterCondition /></RunstatsTableScope ></DB2AutoRunstatsPolicy> ') );
CONNECT RESET;

Prune History :

ELECT ' CONNECT TO ' CONCAT CURRENT SERVER CONCAT ' ;'
FROM SYSIBM.SYSDUMMY1 ;

WITH LISTE(JAHR, MONAT, TAG) AS (
SELECT YEAR(DATUM) AS JAHR
    , MONTH(DATUM) AS MONAT
    , DAY(DATUM) AS TAG
FROM
(SELECT (CURRENT DATE - 3 DAYS) AS DATUM
FROM SYSIBM.SYSDUMMY1) AS TEMP
)
SELECT 'PRUNE HISTORY ' CONCAT TRIM(CHAR(JAHR)) CONCAT
       SUBSTR(DIGITS(MONAT), 9, 2)
CONCAT SUBSTR(DIGITS(TAG),9, 2)
       CONCAT ' WITH FORCE OPTION AND DELETE ;'
FROM LISTE
;

SELECT ' CONNECT RESET ; ' FROM SYSIBM.SYSDUMMY1 ;