Today’s article addresses a recent code challenge that we encountered and successfully solved.
Below, we’ll demonstrate a way to route all dynamic DB2 requests for a particular User ID, such as SQL from Rational for i, IBM DataStudio, DataGrip, Mulesoft, etc. – a game changer for handling SQL requests from various platforms.
Normally these requests are handled by a QZASOINIT job running in the QUSRWRK subsystem. Using this method, you can increase the priority at which the SQL will execute.
Important note: This procedure does not work for secure DB2 requests (QZASSINIT), at levels below OS 7.5.
Let’s get started…
Create a Subsystem to handle the requests
1. Create subsystem description, in this case named PTYSQL.
CRTSBSD SBSD(PTYSQL) POOLS((1 *BASE))
2. Create a Job Queue for the Subsystem.
CRTJOBQ JOBQ(PTYSQLJOBQ)
3. Attach Job Queue to Subsystem.
ADDJOBQE SBSD(PTYSQL) JOBQ(PTYSQLJOBQ)
4. Create Class with elevated run priority and time slice.
CRTCLS CLS(PTYSQLCLS) RUNPTY(19) TIMESLICE(3000)
5. Create a prestarted job entry for QZDAOINIT program. Important: the name of the job must be the same as the subsystem name.
ADDPJE SBSD(QGPL/PTYSQL) PGM(QSYS/QZDASOINIT) JOB(PTYSQL)JOBD(QGPL/QDFTSVR) CLS(PTYSQLCLS)
6. Start the Subsystem.
STRSBS SBSD(QGPL/PTYSQL)
7. Display prestarted Jobs in the Subsystem to verify.
WRKACTJOB SBS(PTYSQL)
Run IBM stored procedure to create SQL routing entry
- From SQL command line in STRSQL or IBM CA
CALL QSYS2.SET_SERVER_SBS_ROUTING('[USERID]', 'QZDASOINIT', 'PTYSQL');
Notes:
USERID – Your user id
QZDASOINIT – Database server
QZDASSINIT – Secure database server (need OS 7..5 to route)
QZRCSRVS – Remote server command (run RPG program)
*ALL – All services (see IBM Docs for a list of all data services, data queue, DDM, etc.)
2. Verify routing is in force
SELECT * FROM qsys2.server_sbs_routing
3. Verify Active Requests
SELECT JOB_NAME, AUTHORIZATION_NAME, ELAPSED_CPU_PERCENTAGE, ELAPSED_TOTAL_DISK_IO_COUNT, ELAPSED_PAGE_FAULT_COUNT FROM TABLE(ACTIVE_JOB_INFO( SUBSYSTEM_LIST_FILTER => 'PTYSQL')) x ORDER BY ELAPSED_CPU_PERCENTAGE DESC
And there you have it!
Need help with code solutions for your legacy application?
Our deep experience with legacy enterprise systems puts us uniquely positioned to help reinvent your modernization efforts that can transform your organization.
Please contact us for more information or to schedule a demonstration of our CM evolveIT software and how it’s impact analysis and automated refactoring capabilities can set your legacy modernization project up for long-term success.
You can also call us at 888-866-6179 or email us at info@cmfirstgroup.com.
