Debugging DB2 Session

1
votes

I'm just the unfortunate sole debugging an iSeries/RPG/SQL issue... (I'm not an RPG expert)

I have a program which contains temporary tables declared on DB2 on the iSeries. The temporary tables are declared in a session, so when I run the application and debug the RPG via a terminal on the iSeries (I presume this is the right terminology?) Anyway, I'm effectively in two different sessions.

The SQL I am looking at does something like this...

select blah from SESSION/#temp_table left join #real_table left join _to_many_other_tables

While I can query the "real table" fine, I can't see the contents of the SESSION table... so how would I go about querying a table in a different session ?? Presumably SESSION/#temp_table is something I could query by doing something like select * from 123123/#temp_table, but how would I know what the other session id/name/variable/access token looks like?

1
sqlibm-midrangedb2-400rpg
As commented below, I couldn't just look at the SQL that generated the temporary table. On DB2 I think you can do this using syntax like "select dataX, dataY from table(RPG_PROGRAM_X([...]) as x, tabley as y where x.id = y.id [...]" - 0909EM

1 Answers

1
votes

You can use STRSRVJOB to debug another job, but this probably won't let you query that job's QTEMP. Traditionally, midrange programmers debug jobs like these interactively. Sign on to a green screen session and CALL the program you want to debug.

Between STRDBG, STRISDB, the system debugger and the SEP facility found in RDi, there are many options to tackle the debugging problem. Additionally, the open source DBG400 might be something to look at.

EDIT: The problem is a difficult one. It looks like this is a client/server type app. When writing an app like this, I usually put a debug switch into it so I can log what's happening for debugging purposes. Stored procedures on DB2 for i can be implemented purely in SQL, or they can call out to an HLL like RPG for the implementation.

If your SPs are external, say RPG, then add some code that will copy the temporary files to a real library on the system. Implementing it as a system() or QCMDEXC call is not very intrusive to the existing program code. You can turn it on and off with a data area - again, very unintrusive, but you can set the debug state from outside the application.

If your SPs are SQL, I'd modify them to write a duplicate of the temporary file in a real library. Say there's a CREATE TABLE QTEMP/TEMP001 WITH DATA ... Add a CREATE TABLE DEBUGLIB/TEMP001 WITH DATA ... If you wanted to, you could key this extra code on a special 'debug' user profile, although that might require some security changes on the IBM i side.