How to set Special Registers for DB2 ZOS systems for a JDBC connection in Data Studio
I need to set Special Registers like CURRENT QUERY OPTIMIZATION, CURRENT PACKAGESET, etc. on the DB2 ZOS connection created in Data Studio. How can I accomplish this?
By setting specialRegisters parameter on the connection level, it will not have to be set manually in each SQL script.
Please note that for using Special Registers you need to have JDBC driver 4.15. Data Studio 3.2 ships with JDBC driver 3.64.106 and 4.14.113 but is not packaged with JDBC driver 4.15.
You can manually modify the connection URL and manually add this option so that it is passed to the JDBC driver.
- Download the new 4.15 JDBC driver from the link below
- Using Data Studio select 'Window -> Preferences'
- Go to the 'Driver Definitions' tab, which can be found under 'Data Management -> Connectivity -> 'Driver Definitions'
- Click the 'Add..' button to create a new driver profile
- For the 'Driver Type Filter:' drop down select 'Database'
- For the 'Vendor Filter:' select 'DB2 for z/OS'
- Under 'Available driver templates:' you can select the first one 'IBM Data Server Driver for JDBC and SQLJ'
- Modify the 'Driver name:' field to something unique, such as 'IBM Data Server Driver for JDBC and SQLJ SpecialRegisters'
- Under the 'Jar List' tab select 'Clear All'
- Click 'Add JAR/Zip...' and add the new JCC driver that was downloaded in Step 1
- Under the 'Properties' tab modify the 'Connection URL' and add 'specialRegisters=CURRENT PACKAGESET =PACK1;' to the end of the URL. (PACK1 is the packageset to be set)
- Enter the 'Database Name', 'Password' and 'User ID'
- Hit Ok and hit Ok again.
- Now create a new Connection Profile. On the left switch to 'DB2 for z/OS'. If you now look under the 'JDBC driver:' drop down you will see the new driver profile created in Step 4-13. Select it
- If you now scroll down in the Properties you will see the specialRegisters value in the 'Connection URL:' box. Whatever is in this box is what Data Studio sends to the JDBC driver
- Complete the rest of the required fields and hit finish to create the profile and to connect to the database