DB2 DSNZPARMS: How to Get DSNZPARM Settings with Data Studio to Improve Big Data Performance

Building a big data warehouse using a Union All View (UAV) table with billions of rows is only the beginning.  There has to be a DB2 system that is enabled so the data warehouse can have SQL optimized automatically and queries run efficiently.  Hopefully each of the critical DB2 DSNZPARM settings was reviewed during your migration to DB2 10 for zOS.

To understand all the DB2 DSNZPARMS within your DB2 10 zOS environment you first have to get a printout of all the settings.  Bothering your DBA or system programmer is not necessary since you can easily get them yourself through IBM Data Studio.
After you get a listing you can figure out the correct settings by reviewing my other blog entries on DB2 Data Warehouse DSNZPARMS (Parts 1-3) and the PDF that explains all the various settings details.

To get all the DB2 DSNZPARMS start up the free IBM DB2 Data Studio product.  If you don’t have a copy just download the latest version from the IBM website here.

If you are setting up your environment for the first time, you will need the DB2 System name and its TCP/IP port for the connection.  Your DBA or system administrator should be able to help you get connected.

Once you have Data Studio downloaded, open it, go into the Data Source Explorer, connect to your DB2 10 zOS system and expand the SCHEMAS. Then expand the SYSPROC entry as shown in the picture below.

To get the DB2 DSNZPARMS we will be using one of the many new administration stored procedures within DB2 10.  The one that is used to get the DB2 DSNZPARMS is the ADMIN_INFO_SYSPARM stored procedure.

Right click on the stored procedure and choose RUN SETTINGS. That will direct it to the DB2 system we want the DB2 DSNZPARMS from.

This will bring up the RUN SETTINGS dialog box.  Click on the PARAMETER VALUES section.

Click in the Value entry box for DB2_MEMBER and click the SET TO NULL button and then make sure to click the checkbox for REMEMBER MY VALUES before clicking OK.

Setting this value to NULL indicates that the current system that is connected and active should be queried for its DB2 DSNZPARMS.  Within a data sharing environment you can supposedly specify other members within the data sharing group, but I haven’t tried it yet since we mirror and synchronize all our DB2 DSNZPARMS across all our DB2 data sharing members.

Now right click on the ADMIN_INFO_SYSPARM store procedure again which brings up the menu and click on RUN to execute your stored procedure with your previous NULL setting to get the current DB2 DSNZPARMS.

If your connection ID has authority, the stored procedure address space is working and the IBM ADMIN stored procedures were installed in your DB2 10 system correctly, your process request should run and produce a result set listing of all the current DB2 DSNZPARMS for your DB2 10 zOS system.

If the stored procedure address space is not active or some other error occurs then you might receive an error message like this:

SYSPROC.ADMIN_INFO_SYSPARM – Calling the stored procedure.
SYSPROC.ADMIN_INFO_SYSPARM – Exception occurred while running:
A database manager error occurred. SQLCODE: -471, SQLSTATE: 55023 –
DB2 SQL Error: SQLCODE=-471, SQLSTATE=55023,
SYSPROC.ADMIN_INFO_SYSPARM – Roll back completed successfully.

Talk with your system administrator or DBA support about your connection, authority and use of this administration stored procedure. Once the problems are fixed, your ADMIN_INFO_SYSPARMS will run successfully and produce a wonderful list of the current DB2 DSNZPARM settings within your DB2 zOS system.


Once you get the listing of all settings check the advice in the DB2 DSNZPARMS blogs mentioned earlier and your system will be ready to give you the best performance with the correct DB2 DSNZPARMS settings for your big data application.

Also thanks for all the comments about last week’s blog. Handling Java programming and mismatched data types needs further coverage and I am working on something to help.

I look forward to supporting the DB2 community through the local DB2 User Groups.

I am coming to Dallas and Austin, Texas October 10th and 11th and look forward to presenting my “Agile Big Data Analytics: Implementing a 22 Billion Row Data Warehouse” and “Java DB2 Developer Performance Best Practices” speeches. Check the website www.db2forum.org for Dallas for more information. The Austin one should be updated soon.

I will be talking more about Big Data, UNION ALL Views and Materialized Query Tables during my presentation at the Information on Demand (IOD) conference in Las Vegas October 21st through 25th. Through my speech “Agile Big Data Analytics: Implementing a 22 Billion Row Data Warehouse” Monday, October 22, 10:15 – 11:15 am in the Mandalay Bay North Convention Center – Islander C. This presentation details the designing, prototyping and implementing a 22+ billion row data warehouse in only six months using an agile development methodology. This complex analytics big data warehouse architecture took processes for this federal government agency from 37 hours to seconds.

Also I look forward to supporting the International DB2 Users Group IDUG conference in Berlin, Germany November 5th-9th with two topics “Data Warehouse Designs for Performance” and “Java DB2 Developer Performance Best Practices” on Tuesday November 6th.

On December 4th 5th and 6th I will be presenting at the Minneapolis, Milwaukee and Chicago DB2 User groups.

Please come by any of these presentations and say, “Hi.”

Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>