Technical Question

[gcraill]

Yo Techies,

When using the STRSQL utility, you can go into the options and print the accumulated interactive SQL log to a spoolfile, so it is possible to interact with the SQL log, and also when you go back into your interactive SQL session via STRSQL even after a period of some months the log of previous SQL commands is there to see and use.

Is there any way to copy those interactive logs to a browsable file for security type bods to trawl through ?

Research to date suggests no, but then until today I also thought a mouse scrollwheel was not compatible with CA either

It's wednesday evening - I am off home, my Wife says there are 50% off sales on so there goes the pay packet !!!

Catch you all on Saturday !!!
GC

[Pete]

Re: Technical Question

Here is a discussion from many years ago. I don't know how useful it will be for you........

http://archive.midrange.com/midrange-l/200007/msg00699.html

Pete

[soup_dog]

Re: Technical Question

What about copying it to a spoolfile and then from the sppolfile back into a physical file? It's not straightforward but should still be fairly easy to do.

[soup_dog]

Re: Technical Question

The following is quoted from this site:

http://www.centerfieldtechnology.com...stions.asp#Q99

This question is for iSeries AS/400 V5R3 with DB2. When we run a STRSQL session we have the option to save and exit. On which file do the queries of the earlier sessions get saved? Can we edit the file to keep only certain frequently used queries for reference? Top of Page
Where and if your interactive SQL session attributes get saved depends upon which option you choose when you exit STRSQL. When you hit F3 to exit interactive SQL you will be prompted with 4 options:

1=Save and exit session
2=Exit without saving session
3=Resume session
4=Save session in source file

Let's describe each one of them in greater detail.

1) Being that default on this option is 1, let's discuss what happens when you simply hit Enter after hitting F3 to exit interactive SQL.
System will save your session in a Permanent Miscellaneous Space MI Object in the QRECOVERY library (type 19 subtype EE). This is not a file and is not designed to be accessed by end-user (i.e. you). If you have programmers in house that are proficient in MI (Machine Interface) coding, they may attempt to dabble with this type of coding and hence accomplish what you're after (i.e. only preserve frequently saved statements in it).
One easy way for regular iSeries users to see what's stored in this space object is to do the following:

DMPSYSOBJ OBJ(ISQLST*) CONTEXT( QRECOVERY) TYPE(19) SUBTYPE(EE)

If your user profile name is NERGISH, you would replace with NERGISH (i.e. ISQLSTNERGISH*). This will create a spooled file QPSRVDMP in your library. If you search this spooled file on term "NAME-" you may get multiple hits. This will occur if you ever work in interactive SQL on more than one AS400 workstation device. What this means is that you'll have multiple space objects in the QRECOVERY library.
Naming convention IBM follows for these internal space object based on byte positions is:
01-06 :: ISQLEN
07-16 :: User profile starting the session.
17-26 :: Workstation device name for session selection
27-30 :: Session number for that workstation ID <0000 is first>

If you saved only one interactive SQL session by using option 1 (Save and exit session) on the Exit Interactive SQL display, you may resume that session at any workstation. However, if you use option 1 to save two or more sessions on different workstations, interactive SQL will first attempt to resume a session that matches your work station. If no matching sessions are available, then interactive SQL will increase the scope of the search to include all sessions that belong to your user ID. If no sessions for your user ID are available, the system will create a new session for your user ID and current workstation.

For example, you saved a session on workstation 1 and saved another session on workstation 2 and you are currently working at workstation 1. Interactive SQL will first attempt to resume the session saved for workstation 1. If that session is currently in use, interactive SQL will then attempt to resume the session that was saved for workstation 2. If that session is also in use, then the system will create a second session for workstation 1.

However, suppose you are working at workstation 3 and want to use the ISQL session associated with workstation 2. You then may need to first delete the session from workstation 1 by using option 2 (Exit without saving session) on the Exit Interactive SQL display.

...

Sounds like it might be possible... but it would be ugly.

[littlepd]

Re: Technical Question

When you hit F3 to exit interactive SQL you will be prompted with 4 options:

1=Save and exit session
2=Exit without saving session
3=Resume session
4=Save session in source file

Option 4 = Save session in source file

[soup_dog]

Re: Technical Question

Option 4 = Save session in source file

Yeah, I think he is looking for a way to automate it though... for audit reasons.

[gcraill]

Re: Technical Question

Yeah, I think he is looking for a way to automate it though... for audit reasons.

Zigzactly - we may assume users would use option 1 to leave STRSQL, but we cannot "force" them to use option 4 say and codify to save to a source member of our naming for ease of capture.

This information is really good chaps - I think we can fly with this.
Thanks especially to soup_dog & Pete for the cool info and links.

If we replace the STRSQL command with one that does this;
- log user/date/time that STRSQL was issued
- calls STRSQl as normal
- logs the date/time that STRSQL was exited
- performs the DUMPSYSOBJ for the userid logged
- this generates the spoolfile
- copy the spoolfile to a physical file
- read the PF and split the dump into sections (it has a set length for each line so should be easy to split off)
- log the entries into a log/audit file for security team.
- exit back to command line

Even exit back to the command line earlier and spawn the audit/logging job off to a batch process in the background.

Now I just have to convince our security dept it's worth trying

Sample of "decoded" dump- 1st 14 chars of each "line" are informational and not the actual commands issued by the user.

...snipped short for brevity...
T                 Where TMCEND = 'Y' And TMCVLT = 'RMLS2' And TMSYID = 'STCGSM'   
T             Order By TMCVSR Desc                                                
SQL6072      ISELECT statement run complete.                                      
TH              Select TMCVSR,tmcvlt                                              
T                 From qusrbrm/QA1AMM                                             
T                 Where TMCEND = 'Y' And TMCVLT = 'RMLS2' And TMSYID = 'STCGSM'   
T             Order By TMCVSR Desc                                                
SQL6072      ISELECT statement run complete.                                      
SQL6102      ISession was saved and started again.                                
SQL7971   ة  ICurrent connection is to relational database STCTEST.

Sample of encoded dump text;(text is delimited by "*" which is handy ...)

...snipped short for brevity...
0251A0   40000000 00000040 40404040 E6888599   8540E3D4 C3C5D5C4 407E407D E87D40C1  *            Where TMCEND = 'Y' A*
0251C0   958440E3 D4C3E5D3 E3407E40 7DD9D4D3   E2F27D40 C1958440 E3D4E2E8 C9C4407E  *nd TMCVLT = 'RMLS2' And TMSYID =*
0251E0   407DE2E3 C3C7E2D4 7D404040 E3404040   40404000 00000000 0040D699 84859940  * 'STCGSM'   T             Order *
025200   C2A840E3 D4C3E5E2 D940C485 A2834040   40404040 40404040 40404040 40404040  *By TMCVSR Desc                  *
025220   40404040 40404040 40404040 40404040   40404040 40404040 40404040 4040E2D8  *                              SQ*
025240   D3F6F0F7 F2000000 000000C9 E2C5D3C5   C3E340A2 A381A385 948595A3 4099A495  *L6072      ISELECT statement run*
025260   40839694 979385A3 854B4040 40404040   40404040 40404040 40404040 40404040  * complete.                      *
025280   40404040 40404040 40404040 40404040   E2D8D3F6 F1F0F200 00000000 00C9E285  *                SQL6102      ISe*
0252A0   A2A28996 9540A681 A240A281 A5858440   81958440 A2A38199 A3858440 81878189  *ssion was saved and started agai*
0252C0   954B4040 40404040 40404040 40404040   40404040 40404040 40404040 40404040  *n.                              *
0252E0   4040E2D8 D3F7F9F7 F1000021 62003AC9   C3A49999 8595A340 83969595 8583A389  *  SQL7971   ة  ICurrent connecti*
025300   96954089 A240A396 40998593 81A38996   95819340 8481A381 8281A285 40E2E3C3  *on is to relational database STC*
025320   E3C5E2E3 4B404040 40404040 40404040   40404040 C1404040 40404000 00000000  *TEST.               A           *

Cheers
GC