www.ruban.de – DB2 experts web site

The DB2 symposium fills the gap between classic conferences and workshops. In classic conferences many topics are covered, but each speaker has only a limited time for each topic. Interaction between speakers and attendees is normally restricted. In a workshop it is the other way around. Two, three or more days are devoted to one topic only. That topic is discussed in detail and the emphasis is normally on interaction. At the DB2 symposium we combine the best of both worlds. We merge the variety of conference topics of with the in-depth approach and the interactivity of workshops. At the DB2 Symposium we offer three full-day sessions each day where the topics are discussed in detail by DB2 experts. They will cover techniques, tips, ideas, lessons learned and planning information you can put to use immediately.

Join us on 28-30 May 2013 in Veldhoven – The Netherlands for a new DB2 Symposium featuring 6 top notch presenters who offer you 9 one-day z/OS seminars on DBA, Programming and System Administration topics. As always you can join us for 3, 2 or just 1 day(s), depending on your needs and budget. One-day seminars and flexible registrations make the DB2 Symposium unique in the world!

http://www.db2-symposium.com/events/veldhoven-2013/

For more information see http://ruban.de/sources-events-links/, section “User Groups”, or click on http://ruban.de/sources-events-links/events/ to list alle DB2 events.

IDUG DB2 Tech Conference in Barcelona, Spain 2013
Barcelona, Spain
October 13-18, 2013

Call For Presentations:
You can submit your abstracts by clicking here.

IDUG DB2 Tech Conference in Berlin Wrap Up Article:
The Conference was a great success attracting attendees who came together to hear about the latest in DB2 and network with their peers. Read the wrap up article here.

Online registration is now open for IDUG DB2 Tech Conference in Barcelona, Spain, October 13-18, 2013!

If you have any questions, please contact IDUG Headquarters.

VIEW FULL DETAILS

The International DB2 Users Group is accepting presentation abstracts for the IDUG DB2 Tech Conference in Barcelona, Spain, October 13-18 2013. This is your opportunity to share your DB2 experience and expertise with an audience of your peers who rely on, or are considering incorporating DB2 in their shop. If your presentation abstract is selected to be delivered at the conference, you will receive one complimentary conference registration the DB2 Conference in Barcelona, Spain.

VIEW FULL DETAILS

For more information see http://ruban.de/sources-events-links/, section “User Groups”, or click on http://ruban.de/sources-events-links/events/ to list alle DB2 events.

IBM provides “The IBM DB2 and IMS Tools Product Lifecycle matrix” to  display the marketing and support services available over the life of z/OS-based products. You can click the dates that are listed to go directly to the product announcement letter. While every effort is made to provide accurate information, the authoritative source for product information is the IBM announcement letters.

See Product Lifecycle for DB2 and IMS Tools for more details.

For products that are not listed in the Product Lifecycle matrix below or for additional IBM Software product lifecycle information that is licensed under the International Program License Agreement (IPLA), see IBM Software Support Lifecycle.

Please follow the direct link to IBM’s download pages of IBM Data Studio Client 3.2

Click here.

No Installation Manager needed!

IBM Data Studio client Linux 32 bit Install – ibm_ds320_lin_im32.zip (1500 MB)
IBM Data Studio client Linux 64 bit Install – ibm_ds320_lin_im64.zip (1500 MB)
IBM Data Studio client Windows Install – ibm_ds320_win.zip (1500 MB)

The SYSPROC.ADMIN_UPDATE_SYSPARM stored procedure changes the value of one or more DB2 subsystem parameters located in one of these macros: DSN6SPRM, DSN6ARVP, DSN6LOGP, DSN6SYSP, DSN6FAC, and DSN6GRP. It builds a subsystem paramters load module, and if requested by the user, loads it into storage by issuing the DB2 command -SET SYSPARM command with the LOAD option. If not all of the subsystem parameters modified are online updatable, -SET SYSPARM command with the LOAD option will not be performed.

DB2 10 for z/OS documentation:

• New system parameter: ADMIN_SET_SYSPARM stored procedure
• Administration: ADMIN_COMMAND_DB2 stored procedure
• Installation:
  • Additional steps for enabling the administrative task scheduler and administrative enablement routines
  • DB2-supplied stored procedures and user-defined functions
  • Core WLM environments for DB2-supplied routines
  • DB2 administration routines panel: DSNTIPRL

DB2 9 for z/OS documentation:

• New system parameter: ADMIN_SET_SYSPARM stored procedure
• Administration: ADMIN_COMMAND_DB2 stored procedure
• Installation:
  • Additional steps for enabling the administrative task scheduler and administrative enablement routines
  • Enabling DB2-supplied routines
  • Setting up a WLM application environment for stored procedures
  • Job DNSTIJSG

IBM® intends to make DB2 11 for z/OS ( DB2 11) available to a select group of clients in a closed Early Support Program (ESP) on March 8, 2013.

Selected features that deliver these valuable benefits to your business include:

• CPU reductions and performance improvements for certain online transaction processing (OLTP), heavy insert, select query workloads, and when running queries against compressed tables
• Improved-data sharing performance and efficiency
• Improved utility performance and additional zIIP eligible workload
• Cost-effective archiving of warm and cold data with easy access to both within a single query
• Intelligent statistics gathering and advanced optimization technology for efficient query execution in dynamic workloads
• Additional online schema changes that simplify management, reduce the need for planned outages, and minimize the need for REORG
• Productivity improvements for DBAs, application developers, and system administrators
• Efficient real-time scoring within your existing transaction environment
• Enhanced analysis, forecasting, reporting, and presentation capabilities, as well as improved storage management, in QMF™
• Expanded SQL, SQL PL, temporal, and XML function for better application performance
• Faster migration with application protection from incompatible SQL and XML changes and simpler catalog migration

Read announcement IBM Europe, Middle East, and Africa Software Announcement ZP12-0467.

DB2 10 for z/OS: The Smarter, Faster Way to Upgrade

By John Campbell, Cristian Molaro, and Surekha Parekh

First Edition

First Printing—October 2011

© 2011 IBM. All rights reserved.

The objective of this book is to help customers plan their migration strategy effectively and efficiently by providing the right information, facts, and guidance. The knowledge you’ll find in these pages will ensure not only that you are adopting IBM best practices but also that you will reap the cost savings and business benefits of DB2 10. We believe the guidance and direction captured here will help our customers gain competitive advantages by doing more with less—more business insight, more performance, and more operational efficiency with less cost.

The launch of this book is timely, with support for IBM DB2 for z/OS Version 8 retiring in April 2012. For the first time in the history of DB2 for z/OS, customers can skip a level and not only take advantage of features in DB2 9 but also realize the immediate cost savings in DB2 10. Upgrading to the latest version of database software can be a time-consuming, expensive, and confusing process. Deciding whether you are the right candidate to skip a release is a difficult decision unless you have access to the right information, facts, and guidance to help you make that judgment. This book will help simplify some of these decisions.

The book is segmented into two parts:

I. Planning for DB2 10 for z/OS upgrade

II. Gaining the financial benefits of DB2 10 for z/OS

DB2 10 for z/OS: The Smarter, Faster Way to Upgrade – click here for free PDF file.

Customers who want to make use of extended EXPLAIN need to adapt and execute a DB2 10 sample job from SDSNSAMP library. IBM delivers a job called DSNTESC to create a set of 20 EXPLAIN tables required.

Because Visual Explain is deprecated and OSC (Optimization Service Center) does not support DB2 10 Unicode explain table, users of DB2 9 or 10 for z/OS will install the lastest version of Data Studio 3.1.1. (See http://ruban.de/db2zos/tools.)

Strange behavior! Be prepared to make some manual corrections which need DBA authorization.

Data Studio 3.1.1 Full Client, the most current version, requires 2 more tables:

The database name AOCQTDB is hard-coded and required by Data Studio 3.1.1. You cannot skip that point.

As already described above, DSNTESC creates a set of 20 tables:

Data Studio 3.1.1 then requires a DSN_STATEMENT_CACHE_TABLE with 48 columnns. Users of DS first have to drop the existing DSN_STATEMENT_CACHE_TABLE before DS recreates it and the corresponding DSN_STATEMENT_CACHE_AUX again.

DS also requires one more table that is not part of the DSNTESC sample job on SDSNSAMP: The DSN_USERQUERY_TABLE and its corresponding DSN_USERQUERY_TABLE_AUX.

Sloppy work!

DB2 Developer’s Guide: A Solutions-Oriented Approach to Learning the Foundation and Capabilities of DB2 for z/OS, 6th Edition
By Craig S. Mullins

DB2 Developer’s Guide is the field’s #1 go-to source for on-the-job information on programming and administering DB2 on IBM z/OS mainframes. Now, three-time IBM Information Champion Craig S. Mullins has thoroughly updated this classic for DB2 v9 and v10. Mullins fully covers new DB2 innovations including temporal database support; hashing; universal tablespaces; pureXML; performance, security and governance improvements; new data types, and much more.

Test drive Chapter 10 to see for yourself why this popular book is in its 6th edition.

Then buy at a 35% discount using coupon code IBM6425 plus free ground shipping within the U.S.

Buy the book for $58.50 (with coupon)

Buy the eBook for $46.80 (with coupon)

Each new release of DB2 introduces new columns or modifies existing columns in the EXPLAIN tables. DB2 has traditionally honored EXPLAIN tables in previous release formats, but it is best practice to use the current-release format in order to gather maximum benefit from your EXPLAIN data.

In addition, EXPLAIN tables were traditionally created in the EBCDIC encoding scheme because data in the DB2 catalog was EBCDIC encoded. Beginning with DB2 V8, almost all catalog data is Unicode encoded, and it is therefore increasingly beneficial to store EXPLAIN data in Unicode tables.

The APAR PK85068 deprecates use of EBCDIC EXPLAIN tables, and use of EXPLAIN tables in previous release formats. Beginning in DB2 V10 conversion mode, use of EXPLAIN tables in DB2 V7 or a prior-version format will fail with SQLCODE -20008, and use of EBCDIC EXPLAIN tables and EXPLAIN tables in DB2 V8 or V9 format will result in SQLCODE +20520. It is recommended that you begin to identify non-compliant tables and eliminate or convert them. To facilitate this task, this APAR provides the following:

• Queries for discovering non-compliant tables. In DB2 V8 and V9, these queries are added to job DSNTIJPM. In DB2 V8 only, they are added to job DSNTIJP9.
• A new sample job, DSNTIJXA, that drives a DB2 REXX exec called DSNTXTA to alter explain tables into current-version format. You can specify to convert all explain tables in DB2 or you can limit the conversion to a particular schema (creator).
• Another new sample job, DSNTIJXB, that drives a DB2 REXX exec called DSNTXTB to generate statements for migrating data from EBCDIC explain tables in a specified schema. DSNTXTB produces two equivalent types statements:
  • Control statements for processing by the DB2 Utilities cross loader (EXEC SQL)
  • SQL statements for processing by SPUFI or a similar dynamic SQL tool.

These statements should not be processed before they are inspected and validated by the user.

• A third new sample job, DSNTIJXC, that shows how to process the statements generated by running DSNTIJXB.

In general, use this process (specific details follow):

• Identify non-compliant explain tables. Reports are provided in job DSNTIJPM (and in DSNTIJP9 in V8 only) that identify all schemas having one or more such tables.
• Bring all explain tables into current release format. You can use job DSNTIJXA to bring all explain tables in a specified schema into current release format. To convert all non-compliant explain tables regardless of the schema, specify an asterisk as the schema.
• Migrate all EBCDIC-encoded explain tables to Unicode. This is a two step process:
  1. Use job DSNTIJXB to generate DB2 cross loader control statements (and equivalent SQL statements) that can be used to copy all EBCDIC explain tables in a specified schema ID to Unicode equivalents. All explain tables belonging to the specified schema must first be in the current release format.
  2. After examing the control statements generated by DSNTIJXB, use job DSNTIJXC to process them. DSNTIJXC assumes that you have purchased the DB2 Utilities Suite. If you have not, you can use the equivalent SQL generated by DSNTIJXB. The process works as follows for each table:
     1. RENAME TABLE to append ‘_EBCDIC’ to the EBCDIC explain table. Renaming the EBCDIC table makes its original name available to its Unicode replacement.
     2. CREATE TABLE to create the explain table as a Unicode table
     3. CREATE AUX TABLE (if the table is a LOB table)
     4. CREATE INDEX for each index on the EBCDIC table. To avoid name conflicts, the new indexes are prefixed with DSNU. If the name already starts with DSN then a ‘U’ is inserted at the fourth position.
        Examples:
        PLAN_TABLE_IDX1 -> DSNU_PLAN_TABLE_IDX1
        DSN_STATEMNT_TABLE_IDX1 -> DSNU_STATEMNT_TABLE_IDX1
     5. DECLARE CURSOR on the EBCDIC table to extract the data
     6. LOAD DATA INTO the Unicode table, using the cursor on the _EBCDIC table
  3. After converting an EXPLAIN table from EBCDIC to Unicode, check for joins to this table. When you convert EXPLAIN tables to UNICODE, their applications which join with EXPLAIN tables can have different results due to the CCSID conversion. For more information, see ‘Objects with different CCSIDs in the same SQL statement’ in the DB2 Internationalization Guide.

Repeat these steps until all explain tables are Unicode encoded and in current release format.

For more information the IBM’s description of APAR PK85068: EXPLAIN TABLE MIGRATION.