www.ruban.de – DB2 experts web site: SQL to retrieve DB2 subsystem information – update for DB2 10 z/OS GETVARIABLE – retrieve DB2 subsystem information miscellaneous » www.ruban.de

Category » miscellaneous

SQL to retrieve DB2 subsystem information – update for DB2 10 z/OS

-- V8:
SELECT GETVARIABLE('SYSIBM.DATA_SHARING_GROUP_NAME'), 
       GETVARIABLE('SYSIBM.PACKAGE_NAME'), 
       GETVARIABLE('SYSIBM.PACKAGE_SCHEMA'), 
       GETVARIABLE('SYSIBM.PACKAGE_VERSION'), 
       GETVARIABLE('SYSIBM.PLAN_NAME'), 
       GETVARIABLE('SYSIBM.SECLABEL'), 
       GETVARIABLE('SYSIBM.SYSTEM_NAME'), 
       GETVARIABLE('SYSIBM.SYSTEM_ASCII_CCSID'), 
       GETVARIABLE('SYSIBM.SYSTEM_EBCDIC_CCSID'), 
       GETVARIABLE('SYSIBM.SYSTEM_UNICODE_CCSID'), 
       GETVARIABLE('SYSIBM.VERSION')
FROM   SYSIBM.SYSDUMMY1;
--
-- V9:
SELECT GETVARIABLE('SYSIBM.APPLICATION_ENCODING_SCHEME'), 
       GETVARIABLE('SYSIBM.COBOL_STRING_DELIMITER'),  
       GETVARIABLE('SYSIBM.DATA_SHARING_GROUP_NAME'),  
       GETVARIABLE('SYSIBM.DATE_FORMAT'),  
       GETVARIABLE('SYSIBM.DATE_LENGTH'),  
       GETVARIABLE('SYSIBM.DECIMAL_ARITHMETIC'),  
       GETVARIABLE('SYSIBM.DECIMAL_POINT'),  
       GETVARIABLE('SYSIBM.DEFAULT_DECFLOAT_ROUND_MODE'),  
--     GETVARIABLE('SYSIBM.DEFAULT_DEFAULT_SSID'),  
       GETVARIABLE('SYSIBM.DEFAULT_LANGUAGE'),  
       GETVARIABLE('SYSIBM.DEFAULT_LOCALE_LC_CTYPE'),  
       GETVARIABLE('SYSIBM.DISTRIBUTED_SQL_STRING_DELIMITER'),  
       GETVARIABLE('SYSIBM.DSNHDECP_NAME'),  
       GETVARIABLE('SYSIBM.DYNAMIC_RULES'),  
       GETVARIABLE('SYSIBM.ENCODING_SCHEME'),  
       GETVARIABLE('SYSIBM.MIXED_DATA'),  
       GETVARIABLE('SYSIBM.NEWFUN'),  
       GETVARIABLE('SYSIBM.PACKAGE_NAME'), 
       GETVARIABLE('SYSIBM.PACKAGE_SCHEMA'),  
       GETVARIABLE('SYSIBM.PACKAGE_VERSION'),  
       GETVARIABLE('SYSIBM.PAD_NUL_TERMINATED'),  
       GETVARIABLE('SYSIBM.PLAN_NAME'),  
       GETVARIABLE('SYSIBM.SECLABEL'),  
       GETVARIABLE('SYSIBM.SQL_STRING_DELIMITER'),  
       GETVARIABLE('SYSIBM.SSID'),  
       GETVARIABLE('SYSIBM.STANDARD_SQL'),  
       GETVARIABLE('SYSIBM.SYSTEM_NAME'),  
       GETVARIABLE('SYSIBM.SYSTEM_ASCII_CCSID'),  
       GETVARIABLE('SYSIBM.SYSTEM_EBCDIC_CCSID'),  
       GETVARIABLE('SYSIBM.SYSTEM_UNICODE_CCSID'),  
       GETVARIABLE('SYSIBM.TIME_FORMAT'),  
       GETVARIABLE('SYSIBM.TIME_LENGTH'),  
       GETVARIABLE('SYSIBM.VERSION') 
FROM   SYSIBM.SYSDUMMY1;
-- 
-- V10: Get Session variables:
SELECT GETVARIABLE('SYSIBM.APPLICATION_ENCODING_SCHEME') 	AS APPLICATION_ENCODING_SCHEME, 
       GETVARIABLE('SYSIBM.COBOL_STRING_DELIMITER')			AS COBOL_STRING_DELIMITER,  
       GETVARIABLE('SYSIBM.DATA_SHARING_GROUP_NAME')		AS DATA_SHARING_GROUP_NAME,  
       GETVARIABLE('SYSIBM.DATE_FORMAT')					AS DATE_FORMAT,  
       GETVARIABLE('SYSIBM.DATE_LENGTH')					AS DATE_LENGTH,  
       GETVARIABLE('SYSIBM.DECIMAL_ARITHMETIC')		        AS DECIMAL_ARITHMETIC,  
       GETVARIABLE('SYSIBM.DECIMAL_POINT')					AS DECIMAL_POINT, 
       GETVARIABLE('SYSIBM.DEFAULT_DECFLOAT_ROUND_MODE')	AS DEFAULT_DECFLOAT_ROUND_MODE,  
--     GETVARIABLE('SYSIBM.DEFAULT_DEFAULT_SSID')			AS DEFAULT_DEFAULT_SSID,  
       GETVARIABLE('SYSIBM.DEFAULT_LANGUAGE')				AS DEFAULT_LANGUAGE,  
       GETVARIABLE('SYSIBM.DEFAULT_LOCALE_LC_CTYPE')		AS DEFAULT_LOCALE_LC_CTYPE,  
       GETVARIABLE('SYSIBM.DISTRIBUTED_SQL_STRING_DELIMITER')   AS DISTRIBUTED_SQL_STRING_DELIMITER,  
       GETVARIABLE('SYSIBM.DSNHDECP_NAME')					AS DSNHDECP_NAME,  
       GETVARIABLE('SYSIBM.DYNAMIC_RULES')					AS DYNAMIC_RULES,  
       GETVARIABLE('SYSIBM.ENCODING_SCHEME')				AS ENCODING_SCHEME,  
       GETVARIABLE('SYSIBM.MIXED_DATA')						AS MIXED_DATA,  
       GETVARIABLE('SYSIBM.NEWFUN')							AS NEWFUN,  
       GETVARIABLE('SYSIBM.PACKAGE_NAME')					AS PACKAGE_NAME, 
       GETVARIABLE('SYSIBM.PACKAGE_SCHEMA')	            	AS PACKAGE_SCHEMA,  
       GETVARIABLE('SYSIBM.PACKAGE_VERSION')				AS PACKAGE_VERSION,  
       GETVARIABLE('SYSIBM.PAD_NUL_TERMINATED')				AS PAD_NUL_TERMINATED,  
       GETVARIABLE('SYSIBM.PLAN_NAME')						AS PLAN_NAME,  
       GETVARIABLE('SYSIBM.SECLABEL')						AS SECLABEL,  
       GETVARIABLE('SYSIBM.SQL_STRING_DELIMITER')			AS SQL_STRING_DELIMITER,  
       GETVARIABLE('SYSIBM.SSID')							AS SSID,  
       GETVARIABLE('SYSIBM.STANDARD_SQL')					AS STANDARD_SQL,  
       GETVARIABLE('SYSIBM.SYSTEM_NAME')					AS SYSTEM_NAME,  
       GETVARIABLE('SYSIBM.SYSTEM_ASCII_CCSID')				AS SYSTEM_ASCII_CCSID,  
       GETVARIABLE('SYSIBM.SYSTEM_EBCDIC_CCSID')			AS SYSTEM_EBCDIC_CCSID,  
       GETVARIABLE('SYSIBM.SYSTEM_UNICODE_CCSID')			AS SYSTEM_UNICODE_CCSID,  
       GETVARIABLE('SYSIBM.TIME_FORMAT')					AS TIME_FORMAT,  
       GETVARIABLE('SYSIBM.TIME_LENGTH')					AS TIME_LENGTH,  
       GETVARIABLE('SYSIBM.VERSION') 						AS VERSION,
       CASE 
	    WHEN SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,8,1) IN ('0','1')
	         THEN 'CM8/CM8*/ENFM8/ENFM8*'
            WHEN SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,8,1) IN ('2','3')
	         THEN 'CM9/CM9*/ENFM9/ENFM9/'
            WHEN SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,8,1) IN ('5')
	         THEN 'NFM10/'
	    ELSE 'FUTURE USE'
       END 													AS MODIFCATION_LEVEL
FROM   SYSIBM.SYSDUMMY1;

-- V8:

SELECT GETVARIABLE('SYSIBM.DATA_SHARING_GROUP_NAME'),

GETVARIABLE('SYSIBM.PACKAGE_NAME'),

GETVARIABLE('SYSIBM.PACKAGE_SCHEMA'),

GETVARIABLE('SYSIBM.PACKAGE_VERSION'),

GETVARIABLE('SYSIBM.PLAN_NAME'),

GETVARIABLE('SYSIBM.SECLABEL'),

GETVARIABLE('SYSIBM.SYSTEM_NAME'),

GETVARIABLE('SYSIBM.SYSTEM_ASCII_CCSID'),

GETVARIABLE('SYSIBM.SYSTEM_EBCDIC_CCSID'),

GETVARIABLE('SYSIBM.SYSTEM_UNICODE_CCSID'),

GETVARIABLE('SYSIBM.VERSION')

FROM SYSIBM.SYSDUMMY1;

-- V9:

SELECT GETVARIABLE('SYSIBM.APPLICATION_ENCODING_SCHEME'),

GETVARIABLE('SYSIBM.COBOL_STRING_DELIMITER'),

GETVARIABLE('SYSIBM.DATA_SHARING_GROUP_NAME'),

GETVARIABLE('SYSIBM.DATE_FORMAT'),

GETVARIABLE('SYSIBM.DATE_LENGTH'),

GETVARIABLE('SYSIBM.DECIMAL_ARITHMETIC'),

GETVARIABLE('SYSIBM.DECIMAL_POINT'),

GETVARIABLE('SYSIBM.DEFAULT_DECFLOAT_ROUND_MODE'),

-- GETVARIABLE('SYSIBM.DEFAULT_DEFAULT_SSID'),

GETVARIABLE('SYSIBM.DEFAULT_LANGUAGE'),

GETVARIABLE('SYSIBM.DEFAULT_LOCALE_LC_CTYPE'),

GETVARIABLE('SYSIBM.DISTRIBUTED_SQL_STRING_DELIMITER'),

GETVARIABLE('SYSIBM.DSNHDECP_NAME'),

GETVARIABLE('SYSIBM.DYNAMIC_RULES'),

GETVARIABLE('SYSIBM.ENCODING_SCHEME'),

GETVARIABLE('SYSIBM.MIXED_DATA'),

GETVARIABLE('SYSIBM.NEWFUN'),

GETVARIABLE('SYSIBM.PACKAGE_NAME'),

GETVARIABLE('SYSIBM.PACKAGE_SCHEMA'),

GETVARIABLE('SYSIBM.PACKAGE_VERSION'),

GETVARIABLE('SYSIBM.PAD_NUL_TERMINATED'),

GETVARIABLE('SYSIBM.PLAN_NAME'),

GETVARIABLE('SYSIBM.SECLABEL'),

GETVARIABLE('SYSIBM.SQL_STRING_DELIMITER'),

GETVARIABLE('SYSIBM.SSID'),

GETVARIABLE('SYSIBM.STANDARD_SQL'),

GETVARIABLE('SYSIBM.SYSTEM_NAME'),

GETVARIABLE('SYSIBM.SYSTEM_ASCII_CCSID'),

GETVARIABLE('SYSIBM.SYSTEM_EBCDIC_CCSID'),

GETVARIABLE('SYSIBM.SYSTEM_UNICODE_CCSID'),

GETVARIABLE('SYSIBM.TIME_FORMAT'),

GETVARIABLE('SYSIBM.TIME_LENGTH'),

GETVARIABLE('SYSIBM.VERSION')

FROM SYSIBM.SYSDUMMY1;

-- V10: Get Session variables:

SELECT GETVARIABLE('SYSIBM.APPLICATION_ENCODING_SCHEME') AS APPLICATION_ENCODING_SCHEME,

GETVARIABLE('SYSIBM.COBOL_STRING_DELIMITER') AS COBOL_STRING_DELIMITER,

GETVARIABLE('SYSIBM.DATA_SHARING_GROUP_NAME') AS DATA_SHARING_GROUP_NAME,

GETVARIABLE('SYSIBM.DATE_FORMAT') AS DATE_FORMAT,

GETVARIABLE('SYSIBM.DATE_LENGTH') AS DATE_LENGTH,

GETVARIABLE('SYSIBM.DECIMAL_ARITHMETIC') AS DECIMAL_ARITHMETIC,

GETVARIABLE('SYSIBM.DECIMAL_POINT') AS DECIMAL_POINT,

GETVARIABLE('SYSIBM.DEFAULT_DECFLOAT_ROUND_MODE') AS DEFAULT_DECFLOAT_ROUND_MODE,

-- GETVARIABLE('SYSIBM.DEFAULT_DEFAULT_SSID') AS DEFAULT_DEFAULT_SSID,

GETVARIABLE('SYSIBM.DEFAULT_LANGUAGE') AS DEFAULT_LANGUAGE,

GETVARIABLE('SYSIBM.DEFAULT_LOCALE_LC_CTYPE') AS DEFAULT_LOCALE_LC_CTYPE,

GETVARIABLE('SYSIBM.DISTRIBUTED_SQL_STRING_DELIMITER') AS DISTRIBUTED_SQL_STRING_DELIMITER,

GETVARIABLE('SYSIBM.DSNHDECP_NAME') AS DSNHDECP_NAME,

GETVARIABLE('SYSIBM.DYNAMIC_RULES') AS DYNAMIC_RULES,

GETVARIABLE('SYSIBM.ENCODING_SCHEME') AS ENCODING_SCHEME,

GETVARIABLE('SYSIBM.MIXED_DATA') AS MIXED_DATA,

GETVARIABLE('SYSIBM.NEWFUN') AS NEWFUN,

GETVARIABLE('SYSIBM.PACKAGE_NAME') AS PACKAGE_NAME,

GETVARIABLE('SYSIBM.PACKAGE_SCHEMA') AS PACKAGE_SCHEMA,

GETVARIABLE('SYSIBM.PACKAGE_VERSION') AS PACKAGE_VERSION,

GETVARIABLE('SYSIBM.PAD_NUL_TERMINATED') AS PAD_NUL_TERMINATED,

GETVARIABLE('SYSIBM.PLAN_NAME') AS PLAN_NAME,

GETVARIABLE('SYSIBM.SECLABEL') AS SECLABEL,

GETVARIABLE('SYSIBM.SQL_STRING_DELIMITER') AS SQL_STRING_DELIMITER,

GETVARIABLE('SYSIBM.SSID') AS SSID,

GETVARIABLE('SYSIBM.STANDARD_SQL') AS STANDARD_SQL,

GETVARIABLE('SYSIBM.SYSTEM_NAME') AS SYSTEM_NAME,

GETVARIABLE('SYSIBM.SYSTEM_ASCII_CCSID') AS SYSTEM_ASCII_CCSID,

GETVARIABLE('SYSIBM.SYSTEM_EBCDIC_CCSID') AS SYSTEM_EBCDIC_CCSID,

GETVARIABLE('SYSIBM.SYSTEM_UNICODE_CCSID') AS SYSTEM_UNICODE_CCSID,

GETVARIABLE('SYSIBM.TIME_FORMAT') AS TIME_FORMAT,

GETVARIABLE('SYSIBM.TIME_LENGTH') AS TIME_LENGTH,

GETVARIABLE('SYSIBM.VERSION') AS VERSION,

CASE

WHEN SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,8,1) IN ('0','1')

THEN 'CM8/CM8*/ENFM8/ENFM8*'

WHEN SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,8,1) IN ('2','3')

THEN 'CM9/CM9*/ENFM9/ENFM9/'

WHEN SUBSTR(GETVARIABLE('SYSIBM.VERSION') ,8,1) IN ('5')

THEN 'NFM10/'

ELSE 'FUTURE USE'

END AS MODIFCATION_LEVEL

FROM SYSIBM.SYSDUMMY1;

How to retrieve JCC version

You need to check the version of your Java Combined Client (JCC) installed? Try this way:

// The following command will retrieve the JCC driver version if executed from the command line: 
java com.ibm.db2.jcc.DB2Jcc -version 

//Or for drivers that are not yet installed: 
java -cp ./db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version 

//Content of db2jcc JAR file:
jar -tvf db2jcc.jar

// The following command will retrieve the JCC driver version if executed from the command line:

java com.ibm.db2.jcc.DB2Jcc -version

//Or for drivers that are not yet installed:

java -cp ./db2jcc.jar com.ibm.db2.jcc.DB2Jcc -version

//Content of db2jcc JAR file:

jar -tvf db2jcc.jar

All DB2 JDBC Driver versions, see here!

More information on DB2, Java, JDBC and JCC.

Comparing the architectural differences between DB2 for z/OS and DB2 for LUW

“Breaking the DB2 Platform Barrier”
Comparing the Architectural
Differences Between DB2 UDB for z/OS
And DB2 UDB LUW

Quest Software, Inc.

Published in 2009 but one of the best overviews for DB2 “newbies” and staff working on both platforms!

Data Aging Tools are usually instrument of development or test environments to change the age of test data, to make them younger or older. In particular, data are systematically adjusted to a desired point on the timeline concerning their chronology. Those tools has an ultimate high point of interest with Y2K or the Year 2000 changeover, where structural changes had to be tested on a complete cover of the calendar. But also in these days, those tools are still interesting when transitions, e.g. new month or new year processing, has to be tested. Actually, these are truly no new processes, but occasionally the one or other application or database management system has to be migrated. And then Data Aging Tools are immanent.

Some major software products as an execeprt of an still emerging market of Data Aging Tools:

A general note on aging issues in TSO Data TIMES
DCMS Data Ager Tool (www.dcmsi.com)
DCMS Date Control System (www.dcmsi.com)
Compuware Xpediter/Xchange (www.compuware.de)
Compuware FileAID/Data Solutions (Data Ager) (www.compuware.de)
CA AllFusion File Age (www.ca.com)
IBM “TicToc” IBM Application Time Facility for z/OS
Princeton Softech’s HourGlass (www.princetonsoftech.com)
joinIT OS – the reporting and data management modul of joinIT for OS390 / zOS inlcudes data aging functions (http://acayosoft.com/file/joinitos.php)

NLS and unicode support

Understanding DB2 Universal Database character conversion (IBM, PDF)

DB2 UDB National Language Support for the Command Line Processor and Utilities (IBM,PDF)

When code page conversion occurs (DB2 V9.7 Info Center)

Supported territory codes and code pages (DB2 V9.7 Info Center)

Ermittlung der Codepage-Einstellungen mit dem DB2 Command “db2 -a connect to ….”.

FOR BIT DATA werden wie Daten mit Codepage=0 verschlüsselt verarbeitet.

Beachte: The IBM DB2 Driver for JDBC and SQLJ returns data from a ResultSet.getString call for a CHAR FOR BIT DATA or VARCHAR FOR BIT DATA column as a lowercase hexadecimal string.

Displaying/selecting [VAR]CHAR FOR BIT DATA table columns:

SELECT CAST(COL_FOR_BIT_DATA AS [VAR]CHAR(nn) FOR SBCS DATA)  
FROM YOURTABLE</span>

1 2	SELECT CAST(COL_FOR_BIT_DATA AS [VAR]CHAR(nn) FOR SBCS DATA) FROM YOURTABLE</span>

What ist the codepage of a table column – querying the DB2 catalog:
(FOR BIT DATA columns are shown with CODEPAGE=0)

SELECT COLNAME, TYPENAME,  CODEPAGE FROM SYSCAT.COLUMNS
WHERE TABNAME = 'YOURTABLE'

1 2	SELECT COLNAME, TYPENAME, CODEPAGE FROM SYSCAT.COLUMNS WHERE TABNAME = 'YOURTABLE'

Unicode Standards: www.unicode.org
Unicode Standard: A Technical Introduction
Introduction to Unicode (ICU User Guide) (Source Forge Net)
Unicode Support speziell für DB2 z/OS Version 8:
All for One and One for All – Introducing Unicode, Part 1 and Part 2 (Quelle: IBM UK)
DB2 UDB for z/OS Internationalization Guide (IBM, FTP, PDF)
II13049: SUPPORT FOR UNICODE: STEPS FOR INSTALLING AND CUSTOMIZING (PART 2)
II13048: SUPPORT FOR UNICODE: STEPS FOR INSTALLING AND CUSTOMIZING (PART 1)
The secret life of Unicode (IBM)
Understanding DB2 Universal Database character conversion (IBM, PDF)
DB2 UDB National Language Support for the Command Line Processor and Utilities (IBM)

Overview relational und object-oriented database systems

Overview relational and object-oriented database systems
relationale database system with OO extensions
manufacturer	product	supported platforms	link
IBM	DB2 Universal Database	AIX, HP-UX, Linux, (0S/2), (OS/390), Sun/Solaris, Windows, z/OS	http://www.ibm.com/software/db2/data
Oracle	Oracle	AIX, Linux, (OS/390), True64, Sun/Solaris, Windows, z/OS	http://www.oracle.de
Sybase	Adaptive Server	AIX, HP-UX, Irix, Linux, Sun/Solaris, True64, Windows	http://www.sybase.de
Borland	Interbase	Linux, Sun/Solaris, Windows	http://www.interbase.com
CA Computer Associates	Ingres/OpenROAD	Unix, OpenVMS, Linux, WIndows	http://www.ca.com
Microsoft	SQL Server	Windows NT/2000/XP	http://www.microsoft.de
object-oriented database systems
manufacturer	product	supported platforms	link
CA Computer Associates	Jasmine	on request	http://www.ca.com
Object Design	ObjectStore	AIX, HP-UX, Irix, True64, Sun/Solaris, Windows	http://www.objectstore.com
Versant	Versant VDS	AIX, HP-UX, Irix, Sun/Solaris, Linux, Windows	http://www.versant.de
post-relationale database systems
manufacturer	products	supported platforms	link
InterSystems	Caché	Win 95/95/ME/NT/2000/XP, Linux, Compaq OpenVMS, Linux, AIX, HP-UX, True64, Sun/Solaris	http://www.intersystems.de
Poet Software	FastObjects	HP-UX, Linux, Sun/Solaris, Windows	http://www.fastobjects.de
Mapping Tools
manufacturer	products	supported platforms	link
Thought	CocoBase Enterprise O/R	n/a	http://www.thoughtinc.com
Poet Software	FastObjects SQL Object Factory	Win 2000/NT/XP/ME, Sun/Solaris	http://www.fastobjects.de
Sun Microsystems	Jaba Blend	n/a	http://www.sun.de
Software Tree	JDX	n/a	http://www.softwaretree.com
ONTOS	ObjectSpark	n/a	http://www.objectspark.com
Rogue Wave Software	SourcePro DB	AIX, AS/400, Windows, Sun/Solaris, HP-UX, (OS/390), True64, Red Hat Linux, z/OS	http://www.roguewave.com

This table requires a revision.

www.ruban.de – DB2 experts web site

Search

DB2 forum

Topics

Ads

Category » miscellaneous

SQL to retrieve DB2 subsystem information – update for DB2 10 z/OS

How to retrieve JCC version

Comparing the architectural differences between DB2 for z/OS and DB2 for LUW

Data Aging Tools

NLS and unicode support

Overview relational und object-oriented database systems