- 论坛徽章:
- 0
|
All of you know about it and most of you have used it at some
point in time – the interactive SQL editor that comes as part of
SQL Development Kit, an IBM charged feature.
Recently I received a question as to the location of the
session statement history once you exit STRSQL and if it’s
possible to prune it out so only certain, good SQL statements
are kept. I knew a little bit about this but wanted to give a
more informed answer so I went all gung-ho and researched
it in greater detail. Boy, was I in for an unexpected ride!
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 the default on this option is 1, let's discuss what
happens when you simply hit Enter after hitting F3 to exit
interactive SQL. The 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 an end-user (i.e. you).
That said, if you really want to see what’s in this space, you
could do the following:
DMPSYSOBJ OBJ(ISQLST<usrprf>*) CONTEXT
( QRECOVERY) TYPE(19) SUBTYPE(EE)
If your user profile is TEST you would replace <usrprf> with
TEST (i.e. ISQLSTTEST*). 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. The
naming convention IBM follows for these internal space
objects based on byte positions is:
01-06 :: ISQLST
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.
2)
I already mentioned option 2 earlier. It could be useful if you
wish to delete some or all of these space objects to conserve
disk consumption on your system. Just sign on to the
workstation device name that you usually use for your
interactive SQL work and select option 2. This will delete the
space object maintained in QRECOVERY. If you keep
reentering STRSQL and selecting option 2 upon exit, you'll
delete all existing space objects.
3)
All the "Resume session" option does is bring you back to the
STRSQL editor. Think of it as Cancel, i.e. you changed your
mind and don't want to exit interactive SQL.
4)
This is where you control your destiny by explicitly telling the
system that you want to save SQL statements from the
interactive SQL session you've run. By default these
sessions will then be saved in the QSQLSESS source
physical file but you can select the library, file and member in
which you want the session information saved. This is a
regular, easily read source member and could prove useful if
you want to archive your STRSQL experiments. |
|