Testing Db2 Design Decisions

WITH GEN_ROWS(N) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N+1 FROM GEN_ROWS
WHERE N < 100)
SELECT N
FROM GEN_ROWS
ORDER BY N

CREATE TABLE DANL.EMPT
(EMPNO BIGINT NOT NULL
,WORKDEPT CHAR(3) NOT NULL
,LASTNAME CHAR(50)
,PRIMARY KEY (EMPNO)
)
IN DANLDB.DANEMPT
CCSID EBCDIC;
CREATE UNIQUE INDEX DANL.XEMP2T
ON DANL.EMPT
(EMPNO ASC)
USING STOGROUP DANLSG
CLUSTER
BUFFERPOOL BP8
CLOSE NO;

INSERT INTO DANL.EMPT
WITH GEN_ROWS(N) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N+1 FROM GEN_ROWS
WHERE N < 100)
,GENDEPT (WORKDEPT) AS
(SELECT ‘D01’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘E01’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘E02’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘F01’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘G04’ FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT ‘X01’ FROM SYSIBM.SYSDUMMY1
)
SELECT A.N
,X.WORKDEPT
,’LUKSETICH’
FROM GEN_ROWS A
INNER JOIN TABLE
(SELECT WORKDEPT, RAND() AS RANDOM_VALUE
FROM GENDEPT
WHERE A.N > 0
ORDER BY RANDOM_VALUE
FETCH FIRST 1 ROW ONLY) AS X(WORKDEPT, RV)
ON 1=1
;

INSERT INTO DANL.EMPT
SELECT EMPNO + 100, WORKDEPT, ‘THOMAS’
FROM DANL.EMPT;

SELECT STMT,
FROM (
SELECT ‘SELECT LASTNAME FROM DANL.EMPT WHERE EMPNO = ‘ ||
STRIP(CHAR(EMPNO)) ||
‘;’
,RAND() AS DANL
FROM DANL.EMPT) AS T(STMT, DANL)
ORDER BY DANL

WITH GEN_ROWS(N) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N+1 FROM GEN_ROWS
WHERE N < 100)
SELECT ‘SELECT LASTNAME FROM DANL.EMPT WHERE EMPNO = ‘ ||
CHAR(INT(RAND()*POWER(10,INT(RAND()*6)))) ||
‘;’
FROM GEN_ROWS

SELECT STRIP(CHAR(EMPNO))
,RAND() AS DANL
FROM DANL.EMPT
ORDER BY DANL;

#! /user/bin/ksh
#
# This script is used to execute db2 batch.
#
#——————————————————-
if [[ $# = 0 ]]
then print “usage: sh db2_db2batch <dbname> <userid> <password>”
exit;
fi
if [[ $1 = “-h” ]]
then print “usage: sh db2_db2batch <dbname> <userid> <password>”
exit;
fi
if [[ $# != 3 ]]
then print “usage: sh db2_db2batch <dbname> <userid> <password>”
exit;
fi

in_dbname=$1
in_uid=$2
in_pw=$3

db2batch -d $in_dbname -f db2batch.input.sql -a $in_uid”/”$in_pw -r db2batch.out.txt,db2batch.out.summary.txt -g off -iso CS

— This set of SQL statements can be used to test a DB2 data server
— It is a good way to practice monitoring or try to stress a box
—
— This next line sets control options
–#SET ROWS_FETCH -1
–#SET ROWS_OUT 1
–#SET PERF_DETAIL 1
–#SET ERROR_STOP NO
–SET# TIMESTAMPT

— This begins a query block. The repeat count follows the block
–#BGBLK 20
SELECT 1 FROM SYSIBM.SYSDUMMY1;
— This ends a query block
–#EOBLK

# SSL stuff starts here
if (-not(“dummy” -as [type])) {
add-type -TypeDefinition @”
using System;
using System.Net;
using System.Net.Security;
using System.Security.Cryptography.X509Certificates;

public static class Dummy {
public static bool ReturnTrue(object sender,
X509Certificate certificate,
X509Chain chain,
SslPolicyErrors sslPolicyErrors) { return true; }

public static RemoteCertificateValidationCallback GetDelegate() {
return new RemoteCertificateValidationCallback(Dummy.ReturnTrue);
}
}
“@
}

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = [dummy]::GetDelegate()
#SSL stuff ends here

# setting up for the REST call
$headers = New-Object “System.Collections.Generic.Dictionary[[String],[String]]”
$headers.Add(“Accept”, ‘application/json’)

# replace the userid and password here
$headers.Add(“Authorization”, ‘Basic DANL1234:********’)

# initialize the output file
$result | Out-File “K:\testoutput.txt”

# in this loop you are reading the test input file of records.
# change the input location to wherever you put the file
# change the input file name to testinput10k.txt to test with 10,000 identifiers
# once you verify it is working comment out the Out-File line for testing remote performance only
foreach($line in [System.IO.File]::ReadLines(“K:\testinput.txt”))
{
$guid = @{GUID=${line}}
$jsonguid = $guid | ConvertTo-Json
$result = Invoke-RestMethod https://dsndb2d0.testdb2.com:741/services/collid1/getTestGuid -Method Post -ContentType ‘application/json’ -Headers $headers -Body $jsonssn
$result | Out-File “K:\testoutput.txt” -Append
}

public class HelloWorld {
public static void main(String[] args) {
System.out.println(“Hello world..!!!”);
}
}

/*
Compile this with `javac DB2Test.java`
Then run it with `java -cp “/C/IBM/db2jcc4.jar:.” DB2Test2 <JDBC URL> <username> <password>`
You might need single quotes or escaping on the password…
This code was taken from http://www.justexample.com/wp/connect-db2-java/ then extended a bit.
It is unclear how the original was licensed. This work is licensed under the MIT license,
Copyright 2017, Jonathan Gnagy.
https://opensource.org/licenses/MIT
*/

import java.sql.*;
import java.util.Properties;
//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.SQLException;

public class DB2Test2 {

public static void main(String[] args) {
String jdbcClassName=”com.ibm.db2.jcc.DB2Driver”;
String url = args[0];
String user = args[1];
String password = args[2];
// String currentPackageSet = args[3];
String server;
String dbname;
Statement stmt1;
ResultSet rs1;

Connection connection = null;
try {
//Load class into memory
Class.forName(jdbcClassName);

//Set properties
Properties prop = new Properties();
prop.put(“user”, user);
prop.put(“password”, password);
// prop.put(“clientApplcompat”, “V11R1”);
// prop.put(“currentPackageSet”, “NULLID”);


//Establish connection
connection = DriverManager.getConnection(url, prop);

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(connection!=null){
try {
stmt1 = connection.createStatement();
rs1 = stmt1.executeQuery(“select current date from sysibm.sysdummy1”);
while (rs1.next()) {
server = rs1.getString(1);
System.out.println(server);
}
rs1.close();
stmt1.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}

/*
Compile this with `javac DB2Test3.java`
Then run it with `java -cp “/C/IBM/db2jcc4.jar:.” DB2Test3 <JDBC URL> <username> <password>`
You might need single quotes or escaping on the password…
This code was taken from http://www.justexample.com/wp/connect-db2-java/ then extended a bit.
It is unclear how the original was licensed. This work is licensed under the MIT license,
Copyright 2017, Jonathan Gnagy.
https://opensource.org/licenses/MIT
*/

import java.sql.*;
import java.util.Properties;
//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.SQLException;

public class DB2Test3 {

public static void main(String[] args) {
String jdbcClassName=”com.ibm.db2.jcc.DB2Driver”;
String url = args[0];
String user = args[1];
String password = args[2];
// String currentPackageSet = args[3];
String server;
String dbname;
Statement stmt1;
ResultSet rs1;

Connection connection = null;
try {
//Load class into memory
Class.forName(jdbcClassName);

//Set properties
Properties prop = new Properties();
prop.put(“user”, user);
prop.put(“password”, password);
// prop.put(“clientApplcompat”, “V11R1”);
// prop.put(“currentPackageSet”, “NULLID”);


//Establish connection
connection = DriverManager.getConnection(url, prop);

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(connection!=null){
try {
stmt1 = connection.createStatement();
connection.setClientInfo(“ClientUser”, “DanL”);
String sql = “UPDATE DANL.ALGDO3 ” +
“SET SYS_SRC_CD = ‘DANJDBC’ ” +
“WHERE NH_GUID = ‘111111111’ ” +
“AND DISB_ONST_DT = ‘2020-05-06’ ” +
“AND DISB_ONST_TYP_CD = ‘123’”;
stmt1.executeUpdate(sql);
connection.setClientInfo(“ClientUser”, “Luksetich”);
sql = “DELETE FROM DANL.ALGDO3 ” +
“WHERE NH_GUID = ‘111111111’ ” +
“AND DISB_ONST_DT = ‘2020-05-06’ ” +
“AND DISB_ONST_TYP_CD = ‘123’”;
stmt1.executeUpdate(sql);
stmt1.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

CREATE PROCEDURE RANGEQUERY1 ()
VERSION V1
ISOLATION LEVEL UR
LANGUAGE SQL
QUALIFIER DANL
DYNAMICRULES BIND
P1: BEGIN

DECLARE EMPNO CHAR(10);
DECLARE LNAME CHAR(50);
DECLARE EOT INTEGER;
DECLARE RANGEQUERY VARCHAR(3000);
DECLARE RANGESQL STATEMENT;
DECLARE RANGECSR CURSOR FOR RANGESQL;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET EOT = 1;

SET EOT = 0;

FOR T AS
WITH GEN_ROWS(N) AS
(SELECT 1 FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N+1 FROM GEN_ROWS
WHERE N < 100)
SELECT CHAR(INT(RAND()*POWER(10,INT(RAND()*6)))) AS EMPNO
FROM GEN_ROWS
DO
SET EMPNO = T.EMPNO;
SET RANGEQUERY =
‘SELECT LASTNAME FROM DANL.EMPT E WHERE EMPNO = ‘
|| EMPNO;
PREPARE RANGESQL FROM RANGEQUERY;
OPEN RANGECSR;
SET EOT = 0;
WHILE EOT = 0 DO
FETCH RANGECSR INTO LNAME;
END WHILE;
CLOSE RANGECSR;
COMMIT;
END FOR;
END P1

/*REXX*/
/* CHECK TO SEE IF THE REXX/DB2 COMMAND ENVIRONMENT IS AVAILABLE */
/* IF IT IS NOT, THEN ESTABLISH IT. */
/* THEN CONNECT TO THE APPROPRIATE DATABASE (SUBSYSTEM) */
/* */
/* TRACE ?I */
‘SUBCOM DSNREXX’
IF RC THEN
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
/* */
/* CHANGE ‘DSN1’ IN THE LINE BELOW TO YOUR TARGET SUBSYSTEM */
/* */
ADDRESS DSNREXX ‘CONNECT’ ‘DSN1’
IF SQLCODE \= “0” THEN
DO
SAY “FAILURE TO CONNECT TO DATABASE”
EXIT 8
END

ADDRESS DSNREXX
/* */
/* THE FOLLOWING TWO LINES IDENTIFY THE STORED PROC TO BE CALLED */
/* AND INITIALIZE THE HOST VARIABLE AS A NUMBER. */
/* */
SPC = “DANL.DANLSP051”
CH1 = “‘N'”
CH2 = “‘0000048003′”
SC = “0”
/* */
/* NOW CALL THE STORED PROCEDURE. */
/* */
EXECSQL “CALL :SPC (:CH1, :CH2, :SC)”
SAY “SQL CODE IS ” SQLCODE
SAY “SQL CODE IS FROM SP IS ” SC
SAY “CH1 IS ” CH1
SAY “CH2 IS ” CH2
EXECSQL “ASSOCIATE LOCATOR (:LOC1) WITH PROCEDURE :SPC”
IF SQLCODE \= “0” THEN
DO
SAY “ASSOCIATE LOCATOR FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
EXECSQL “ALLOCATE C101 CURSOR FOR RESULT SET :LOC1”
IF SQLCODE \= “0” THEN
DO
SAY “ALLOCATE CURSOR FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
DO UNTIL SQLCODE \= “0”
“EXECSQL FETCH C101 INTO :F1, :F2, :F3, :F4, :F5, :F6, F7”
IF SQLCODE = “0” THEN
DO
SAY F1
END
END
SAY “FINAL SQLCODE IS ” SQLCODE
“EXECSQL CLOSE C101”

/* CLEANUP. */
/* DISCONNECT FROM THE DATABASE, AND RELEASE ALLOCATIONS. */
/* */
ADDRESS DSNREXX “DISCONNECT”
S_RC = RXSUBCOM(‘DELETE’,’DSNREXX’,’DSNREXX’)
EXIT 0

/*REXX*/
CALL INITIALIZE
/* SET A VARIABLE EQUAL TO THE DESIRED SQL STATMENT. */
SQLSTMT=”SELECT A.LASTNAME, A.WORKDEPT”,
” FROM DANL.EMP A”,
” WHERE A.EMPNO = ‘000010’”
/* PREPARE THE STATEMENT FOR EXECUTION BY DECLARING THE CURSOR, */
/* DOING A PREPARE OF THE STATEMENT, AND OPENING THE CURSOR.*/
ADDRESS DSNREXX
“EXECSQL DECLARE C1 CURSOR FOR S1”
IF SQLCODE \= “0” THEN
DO
SAY “DECLARE CURSOR FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
“EXECSQL PREPARE S1 FROM :SQLSTMT”
IF SQLCODE < “0” THEN
DO
SAY “PREPARE FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
/* OPEN/FETCH/CLOSE 50 TIMES */
DO 50
“EXECSQL OPEN C1 “
IF SQLCODE \= “0” THEN
DO
SAY “OPEN FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
DO UNTIL SQLCODE \= “0”
“EXECSQL FETCH C1 INTO :TESTMSG, :t2 “
IF SQLCODE = “0” THEN
DO
/* SAY TESTMSG */
END
END
/* HERE THE FETCH LOOP HAS ENDED (SQLCODE <> 0), AND WE CHECK WHY.*/
SELECT
WHEN SQLCODE = 0 THEN DO
END
WHEN SQLCODE = 100 THEN DO
END
OTHERWISE
SAY “FETCH FAILED WITH SQLCODE = ” SQLCODE
SAY SQLERRMC
END
/* ALL IS WELL, SO THE CURSOR IS CLOSED. */
“EXECSQL CLOSE C1”
IF SQLCODE \= “0” THEN
DO
SAY “CLOSE FAILED WITH SQLCODE = ” SQLCODE
END
/* END THE DO LOOP HERE */
END
/* TRACE ADDED TO CAPTURE INFO IN ONLINE MONITOR */
trace ?I
/* CLEANUP. */
ADDRESS MVS “DELSTACK”
ADDRESS DSNREXX “DISCONNECT”
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
EXIT 0
INITIALIZE:
/* CHECK TO SEE IF THE REXX/DB2 COMMAND ENVIRONMENT IS AVAILABLE */
/* IF IT IS NOT, THEN ESTABLISH IT. */
/* THEN CONNECT TO THE APPROPRIATE DATABASE (SUBSYSTEM) */
‘SUBCOM DSNREXX’
IF RC THEN
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
/* CHANGE ‘DSN1’ IN THE LINE BELOW TO YOUR TARGET SUBSYSTEM */
ADDRESS DSNREXX “CONNECT” “DSN1”
IF SQLCODE \= “0” THEN
DO
SAY “FAILURE TO CONNECT TO DATABASE”
EXIT 8
END
/* INITIALIZE STACK AND FREE ANY */
ADDRESS MVS “DELSTACK”
ADDRESS MVS “NEWSTACK”
RETURN;

/*REXX*/
CALL INITIALIZE
/* SET A VARIABLE EQUAL TO THE DESIRED SQL STATMENT. */
SQLSTMT=”SELECT A.LASTNAME, A.WORKDEPT”,
” FROM DANL.EMP A “,
” WHERE A.EMPNO = ?”
/* PREPARE THE STATEMENT FOR EXECUTION BY DECLARING THE CURSOR, */
/* DOING A PREPARE OF THE STATEMENT, AND OPENING THE CURSOR. */
ADDRESS DSNREXX
“EXECSQL DECLARE C1 CURSOR FOR S1”
IF SQLCODE \= “0” THEN
DO
SAY “DECLARE CURSOR FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
“EXECSQL PREPARE S1 FROM :SQLSTMT”
IF SQLCODE < “0” THEN
DO
SAY “PREPARE FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
/* */
DO 50
PARSE PULL empcusr .
“EXECSQL OPEN C1 USING :empcusr “
IF SQLCODE \= “0” THEN
DO
SAY “OPEN FAILED WITH SQLCODE = ” SQLCODE
EXIT 8
END
/* FETCH LOOP. */
DO UNTIL SQLCODE \= “0”
“EXECSQL FETCH C1 INTO :TESTMSG, :t2 “
IF SQLCODE = “0” THEN
DO
END
END
/* HERE THE FETCH LOOP HAS ENDED (SQLCODE <> 0), AND WE CHECK WHY.*/
SELECT
WHEN SQLCODE = 0 THEN DO
END
WHEN SQLCODE = 100 THEN DO
END
OTHERWISE
SAY “FETCH FAILED WITH SQLCODE = ” SQLCODE
SAY SQLERRMC
END
/* ALL IS WELL, SO THE CURSOR IS CLOSED. */
“EXECSQL CLOSE C1”
IF SQLCODE \= “0” THEN
DO
SAY “CLOSE FAILED WITH SQLCODE = ” SQLCODE
END
/* END THE DO LOOP HERE */
END
/* TRACE PLACED AT END TO CATCH DATA IN ONLIN MONITOR */
trace ?I
/* CLEANUP. */
/* DISCONNECT FROM THE DATABASE, AND RELEASE ALLOCATIONS. */
ADDRESS MVS “DELSTACK”
ADDRESS DSNREXX “DISCONNECT”
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
EXIT 0
INITIALIZE:
/* CHECK TO SEE IF THE REXX/DB2 COMMAND ENVIRONMENT IS AVAILABLE */
/* IF IT IS NOT, THEN ESTABLISH IT. */
/* THEN CONNECT TO THE APPROPRIATE DATABASE (SUBSYSTEM) */
‘SUBCOM DSNREXX’
IF RC THEN
S_RC = RXSUBCOM(‘ADD’,’DSNREXX’,’DSNREXX’)
/* */
/* CHANGE ‘DSN1’ IN THE LINE BELOW TO YOUR TARGET SUBSYSTEM */
/* */
ADDRESS DSNREXX “CONNECT” “DSN1”
IF SQLCODE \= “0” THEN
DO
SAY “FAILURE TO CONNECT TO DATABASE”
EXIT 8
END
/* INITIALIZE STACK AND FREE ANY */
/* LEFT OVER ALLOCATED FILES */
ADDRESS MVS “DELSTACK”
ADDRESS MVS “NEWSTACK”
X = OUTTRAP(TRASH.) /* SUPPRESS MESSAGES */
ADDRESS TSO “FREE F(SYSUT1,SYSUT2)”
X = OUTTRAP(OFF) /* SUPPRESS MESSAGES */
/* ALLOCATE THE INPUT FILE */
/* FOR THIS EXAMPLE THE INPUT IS A LIST OF EMPNO */
/* VALUES, ONE PER RECORD, FOR EXAMPLE */
/* 000010 */
/* 000110 */
ADDRESS TSO “ALLOC F(SYSUT1) DA(idinpt.text) SHR “
IF RC /=0 THEN DO
SAY “FAILURE TO ALLOCATE INPUT DATASET”
EXIT 8
END
/* READ THE INPUT FILE INTO THE STACK */
ADDRESS TSO “EXECIO * DISKR SYSUT1 (FINIS”
IF RC /=0 THEN DO
SAY “FAILURE TO READ INPUT DATASET”
EXIT 8
END
ADDRESS TSO “FREE F(SYSUT1)”
RETURN;