DSNTESA
CHECK SQL STRING DELIMITER OPTION AND DECIMAL POINT OPTION CHANGE ACTIVITY 11/07/2012 Add SET CURRENT SQLID dn1651_inst1 / dn1651 05/17/2013 FIX COPYRIGHT STATEMENT 49779_077_724 - - Set the current authorization ID to perform the work - SET CURRENT SQLID =.
-----------------------------------------------------------------------
-- NAME = DSNTESA
--
-- DESCRIPTIVE NAME = CHECK DELIMITER AND DECIMAL POINT SETTINGS
--
-- LICENSED MATERIALS - PROPERTY OF IBM
-- 5698-DB2
-- COPYRIGHT IBM CORP 1982, 2022
--
-- STATUS = VERSION 13
--
-- FUNCTION = CHECK SQL STRING DELIMITER OPTION AND DECIMAL POINT
-- OPTION
--
-- CHANGE ACTIVITY
-- 11/07/2012 Add SET CURRENT SQLID dn1651_inst1 / dn1651
-- 05/17/2013 FIX COPYRIGHT STATEMENT 49779_077_724
-- 12/01/2021 Remove support for deprecated TSs e6837 / s15746
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-- Set the current authorization ID to perform the work
-----------------------------------------------------------------------
SET CURRENT SQLID = 'SYSADM';
CREATE DATABASE TEMPDB CCSID EBCDIC;
CREATE TABLESPACE TEMPSPAC IN TEMPDB CCSID EBCDIC MAXPARTITIONS 254;
CREATE TABLE TEMPTABLE
(NAME CHAR(8),
MIDTERM SMALLINT,
FINAL SMALLINT,
SUM SMALLINT,
GRADE CHAR(1) )
IN TEMPDB.TEMPSPAC
CCSID EBCDIC;
INSERT INTO TEMPTABLE ( NAME, MIDTERM, FINAL )
VALUES ( 'JOE', 55, 68 );
INSERT INTO TEMPTABLE ( NAME, MIDTERM, FINAL )
VALUES ( 'SUE', 83, 87 );
INSERT INTO TEMPTABLE ( NAME, MIDTERM, FINAL )
VALUES ( 'KEN', 50, 65 );
INSERT INTO TEMPTABLE ( NAME, MIDTERM, FINAL )
VALUES ( 'JAN', 92, 95 );
INSERT INTO TEMPTABLE ( NAME, MIDTERM, FINAL )
VALUES ( 'LES', 67, 75 );
INSERT INTO TEMPTABLE ( NAME, MIDTERM, FINAL )
VALUES ( 'SALLY', 75, 78 );
SELECT AVG(FINAL) FROM TEMPTABLE;
SELECT AVG(MIDTERM) FROM TEMPTABLE;
UPDATE TEMPTABLE SET SUM = .6 * FINAL + .4 * MIDTERM;
SELECT AVG(SUM) , MIN(SUM), MAX(SUM) FROM TEMPTABLE;
UPDATE TEMPTABLE SET GRADE = 'F' WHERE SUM < 61;
UPDATE TEMPTABLE SET GRADE = 'D' WHERE SUM BETWEEN 61 AND 70;
UPDATE TEMPTABLE SET GRADE = 'C' WHERE SUM BETWEEN 71 AND 80;
UPDATE TEMPTABLE SET GRADE = 'B' WHERE SUM BETWEEN 81 AND 90;
UPDATE TEMPTABLE SET GRADE = 'A' WHERE SUM > 90;
SELECT * FROM TEMPTABLE;
ROLLBACK;
SELECT NAME, BINDDATE
FROM SYSIBM.SYSPLAN
WHERE CREATOR = USER ;
SELECT COLLID, NAME, VERSION, BINDTIME
FROM SYSIBM.SYSPACKAGE
WHERE OWNER = USER ;
SELECT NAME, CREATOR, BINDDATE, VALID, OPERATIVE
FROM SYSIBM.SYSPLAN
WHERE OPERATIVE = 'N' OR VALID = 'N';
SELECT COLLID, NAME, VERSION, BINDTIME, VALID
FROM SYSIBM.SYSPACKAGE
WHERE OPERATIVE = 'N' OR VALID = 'N';
SELECT DNAME, BTYPE, BCREATOR, BNAME
FROM SYSIBM.SYSPLANDEP
WHERE BCREATOR = USER
ORDER BY DNAME, BTYPE, BCREATOR, BNAME;
SELECT DCOLLID, DNAME, BTYPE, BQUALIFIER, BNAME
FROM SYSIBM.SYSPACKDEP
WHERE BQUALIFIER = USER
ORDER BY DCOLLID, DNAME, BTYPE, BQUALIFIER, BNAME;
SELECT NAME, TYPE
FROM SYSIBM.SYSTABLES
WHERE CREATOR = USER
ORDER BY NAME, TYPE;
SELECT *
FROM SYSIBM.SYSTABLES
WHERE NAME = 'DEPT';
SELECT NAME, COLTYPE, LENGTH, SCALE, NULLS, REMARKS, COLNO
FROM SYSIBM.SYSCOLUMNS
WHERE TBNAME= 'DEPT' AND TBCREATOR = 'DSN8!!0'
ORDER BY COLNO;
SELECT * FROM SYSIBM.SYSPLANAUTH WHERE GRANTEE = USER;
SELECT * FROM SYSIBM.SYSPACKAUTH WHERE GRANTEE = USER;
SELECT * FROM SYSIBM.SYSUSERAUTH WHERE GRANTEE = USER;
SELECT * FROM SYSIBM.SYSDBAUTH WHERE GRANTEE = USER;
SELECT * FROM SYSIBM.SYSTABAUTH WHERE GRANTEE = USER;
SELECT * FROM SYSIBM.SYSCOLAUTH WHERE GRANTEE = USER;
SELECT * FROM SYSIBM.SYSRESAUTH WHERE GRANTEE = USER;
SELECT TCREATOR, TTNAME, STNAME, GRANTOR
FROM SYSIBM.SYSTABAUTH
WHERE GRANTEE = USER;
SELECT BNAME, BTYPE, GRANTOR, NAME
FROM SYSIBM.SYSPLANAUTH, SYSIBM.SYSPLANDEP
WHERE GRANTEE = USER
AND NAME = DNAME
AND EXECUTEAUTH <> ' '
AND ( BTYPE = 'T' OR BTYPE = 'V');
SELECT DCOLLID, BNAME, BTYPE, BQUALIFIER, BNAME
FROM SYSIBM.SYSPACKAUTH, SYSIBM.SYSPACKDEP
WHERE GRANTEE = USER
AND LOCATION = DLOCATION
AND COLLID = DCOLLID
AND NAME = DNAME
AND EXECUTEAUTH <> ' '
AND ( BTYPE = 'T' OR BTYPE = 'V');
SELECT NAME, CREATOR, TYPE, DBNAME, TSNAME
FROM SYSIBM.SYSTABLES
WHERE DBNAME IN
(SELECT NAME FROM SYSIBM.SYSDBAUTH
WHERE GRANTEE = USER
AND DBADMAUTH <> ' '); 