Donnerstag, 24 of April of 2014

Category » DB2 for z/OS

GSE DB2 z/OS Guide + DB2 V11 Workshop (german)

IBM freut sich, Sie zur Frühjahrstagung des deutschen GSE DB2 z/OS Guides nach Würzburg einladen zu können. Die Veranstaltung steht diesmal unter dem Motto “DB2 z/OS V11″!

Während der GSE Tagung werden viele interessante Vorträge rund um DB2 V11 von Kundensprechern die auch am Early Support Programm teilgenommen haben, IBM Sprechern und ein Beitrag zur U30 System z Talentinitiative angeboten. Alle Teilnehmer die Interesse an einer Vertiefung zu den neuen DB2 V11 Erweiterungen haben, sind ebenfalls herzlich eingeladen am anschließenden DB2 V11 Pilot-Workshop teilzunehmen.

Die Gesamtveranstaltung “GSE DB2 z/OS Guide” + “DB2 V11 Pilot-Workshop” wird vom 28. – 30. April 2014 in Würzburg stattfinden. Wir bitten Sie Ihre Teilnahme zum GSE DB2 z/OS Guide und nach Wunsch auch am DB2 V11 Pilot-Workshop separat anzumelden.

GSE DB2 z/OS Guide

28.04.2014, Beginn 12:00 Uhr

29.04.2014, Ende der GSE Tagung 13:00 Uhr

Veranstaltungsort: Im Saalbau Luisengarten in Würzburg, Martin-Luther-Straße 1, D-97070 Würzburg

DB2 V11 – Pilot Workshop

29.04.2014, Beginn um 14:00 Uhr im Saalbau Luisengarten

30.04.2014, 09:00 – 12:00 Uhr im Hotel Rebstock, Galerie Greising

Für die Teilnahme am DB2 V11 Pilot-Workshop wird eine Tagungspauschale in Höhe von 40,– € vom Hotel Rebstock erhoben. Diese ist am Abreisetag an der Rezeption vom Hotel Rebstock zu begleichen.

Mehr Informationen finden Sie hier.

Loading DB2 z/OS data into DB2 LUW in internal format

Loading data of DB2 z/OS internal format into a DB2 Linux/Unix/Windows database became very easy.

  • Either you use DB2 Connect to connect from any DB2 Linux/Unix/Windows to DB2 for z/OS. But this costs you a separate license fee. For a distinct class of DB2 LUW server (e.g. on AIX) IBM supplies the DB2 Connect Enteprise license, which costs even more.
  • Or just use existing infrastructure components: UNLOAD or DSNTIAUL your data at z/OS, transport (e.g. SCP, SFTP ) your data in binary format and LOAD, IMPORT or INGEST data at DB2 for Linux/Unix/Windows. Following sample will show you how it works.

Assume following DB2 structrure of a sample table:

Then unload your DB2 z/OS data using DSNTIAUL. SYSPUNCH will show you the columns locations you will need later:

Then transport SYSRECnn dataset to Linux/Unix/Windows in binary format. Use SCP or SFTP for example.

Then LOAD sample data into your DB2 Linux/Unix/Windows database. Specifiy codepage of origin, e.g. 819 or 237. Also specify optional keywords BINARYNUMERICS and PACKEDDECIMAL to allow loading data in IBM proprietary data formats. If you are using NULL indicators you have to specify as NULLINDCHAR.

Restrictions may exist for CLOB, XML and other ‘advanced’ data types.

SQL playing with user id’s

Does your application has to check cerrtain authorzation id’s for certain access rights needed? Following SQL shows some ways how to proceed:

In this sample the SQL’s have to check incoming userid’s for having ..

  • L0nnnnnn or F0nnnnnn
  • where n=0-9
  • except first letter should not have any other letter or special character

Any comment? Do not hesitate to provide YOUR solution! Send your suggestions to!

db2jcc_license_cisuz.jar JCC JDBC

Accessing DB2 for z/OS or DB2 for iSeries from client on DB2 for Linux/Unix/Windows? Do you  experience troubles accessing then remote server database? Missing a license file? How to install and custimize?

New document “Extended DB2 DRDA Security” available

Extended DB2 DRDA Security:

  • Managing TCP/IP-based connection requests
  • Prepare requestors for access requests using extended security
  • Setting DB2 for z/OS DRDA AR to use encrypted passwords
    • Using ICSF on z/OS
  • Using PassTicket
  • How RACF Processes the Password or PassTicket
  • The role of TCPALVER system parameter
  • Troubles using PassTickets
    • Unable to obtain a PassTicket – 00D31059
    • Invalid PassTicket passed – 00F30085
  • Protected Users and PassTickets
  • Surrogate User Jobs Submission

Read more or see other documents on DB2 z/OS page.

Job Scheduling on Windows – White Paper

The purpose of the white paper is to compare mainframe-based and distributed-based job schedulers and to assess the readiness of Microsoft Windows-based job scheduling technology to manage workloads at the enterprise level. The paper can also help IT organizations that plan to migrate mainframe workloads to the Windows Server platform as part of a legacy modernization initiative.

This paper opens with a review of the market for job scheduling, followed by a brief outline of its growth path. Detailed comparisons of a selection of both mainframe-based and distributed-based job scheduling products provide the basis for analyzing job scheduling capabilities on each type of platform. It also looks at two products on the market that offer JCL emulation on the Windows Server platform. It concludes with a glimpse at what Microsoft is doing with scripting and task scheduling.

Custom Research Note, Dr. Mike Gilbert, Legacy Directions
Technical Review: Michael Dee Hester, Microsoft
Published: December 2006

Read Word DOC here.

For the latest information, please see .

SQL functions to convert DEC to HEX value

Do you need to convert decimal value to hexadecimal representation? Then you should try this:


Sample data displayed in a GUI (e.g. DbVisualizer):


The result is of type VARBINARY. You cannot mix  CHAR / VARCHAR and VARBINARY data but you can concatenate other converted strings. Example:


In former versions of DB2 the attribute CHAR FOR BIT DATA has been used. The VARBINARY attribute is the appropriate replacement.

CA Performance Handbook for DB2 for z/OS

The “CA Performance Handbook for DB2 for z/OS” handbook helps DB2 performance tuners to complete the following tasks:

  • Design databases for high performance.
  • Understand how DB2 processes SQL and transactions, and tune those processes for performance.

For database performance management, the philosophy of performance tuning begins with database design and extends through the database and application development and production implementation.


Key Topics:

This guide includes the following key topics:
  • Fundamentals of DB2 performance.
  • Descriptions of features and functions that help you manage and optimize performance.
  • Methods for identifying DB2 performance problems.
  • Guidelines for tuning static and dynamic SQL.
  • Guidelines for the proper design of tables and indexes for performance.
  • DB2 subsystem performance information, and advice for subsystem configuration and tuning.
  • Commentary for proper application design for performance.
  • Real world tips for performance tuning and monitoring


CA Technologies Product References

This document references the following CA Technologies products:
  • CA Database Analyzer™ for DB2 for z/OS (CA Database Analyzer)
  • CA Detector® for DB2 for z/OS (CA Detector)
  • CA Index Expert™ for DB2 for z/OS (CA Index Expert)
  • CA Insight™ Database Performance Monitor for DB2 for z/OS (CA Insight DPM)
  • CA Plan Analyzer® for DB2 for z/OS (CA Plan Analyzer)
  • CA Rapid Reorg® for DB2 for z/OS (CA Rapid Reorg
  • CA SQL Ease® for DB2 for z/OS (CA SQL Ease)
  • CA Subsystem Analyzer for DB2 for z/OS (CA Subsystem Analyzer)

Read the “CA Performance Handbook for DB2 for z/OS“.

New document: DB2 11 for z/OS new way of archiving data

New document published:

DB2 11 for z/OS new way of archiving data

DB2 11 for z/OS new way of archiving data: With DB2 11 for z/OS user can create archive tables to manage historical data for existing tables that is not often referenced. Read more about new table type called ‘archive table’.

DB2 11 for z/OS new way of archiving data


List of important APAR’s for DB2 z/OS

Work File
Enhancement of the process for creating Work File table spaces during installation and migration.
DB2 9 and 10 for z/OS
Work File Recommendations.
DB2 9 and 10 for z/OS

Keywords: Work File, Sortwork, DSNDB07, DGTT, TEMP Space


Storage usage error informational APAR.
DB2 9, 10 and 11 for z/OS
New configuration parameters to control real storage consumption.
DB2 10 for z/OS



DDF migration information relative to DB2 10 for z/OS, Info APAR summary.

DB2 10 for z/OS
Change authorization check at server for DB2 for z/OS to recognize secondary IDs from DB2 for z/OS requesters when Private Protocol is disabled.
DB2 10 for z/OS
System parameter DDF_COMPATIBILITY disabled if application compatibility level is equal to or greater than V11R1.
DB2 10 and DB2 11 for z/OS

Keywords: DDF, Private Protocol, DB2 Data Server Client, ODBC, CLI, JDBC, SQLJ


Support for SQL SELECT from directory tables.
DB2 10 for z/OS
Info APAR to link together all the XML support delivery APARs.
DB2 10 and 11 for z/OS



DB2-supplied Stored Procedures,
see also
New stored procedure to update ZPARMs.
DB2 10 for z/OS
DB2 10 and 11 for z/OS
Replacement for DB2PLI8 and the OSC Service SQL tool that collects user environment information as required to diagnosis problem queries. This tool will now be part of the DB2 product and will be available any time when the DB2 service team requires re-creation documents.
DB2 9, 10 and 11 for z/OS



New function to enable LOAD PRESORTED and UNLOAD FORMAT INTERNAL to improve performance.
DB2 9 and 10 for z/OS
Info APAR lists the NFM PTFs for DSNTEP2, DSNTEP4, DSNTIAUL, DSNTIAD including the mandatory PTF level you must be at before migrating. There is also a discussion on when to rebind them and the use of NEWFUN= and new function IDAA support.
DB2 10 and 11 for z/OS
Fix for DSNTIAUL to support unloading XML data (login required).
DB2 9 and 10 for z/OS



Miscellaneous DB2 for z/OS installation and samples fixes.
DB2 9, 10 and 11 for z/OS
Miscellaneous DB2 install changes
DB2 9 and 10 for z/OS

Keywords: Migration, DSNTIP, Installation, OEM, BMC


OEM products
see also
Informational APAR’s: DB2 OEM product problem info

Keywords: OEM, CA, BMC


Separating system privileges from system DBADM authority
DB2 10 for z/OS

Keywords: implicit system privileges