New AS400 query question -- retrieve current user id??

[arniec]

My boss wants a query that is user aware. For example, if Jim Smith, user ID 123456, runs the query then the results will be records matching user ID 123456. Meanwhile, if Jane Doe, user ID 654321, runs the query at the same time it will return results matching user ID 654321.

Now the sticky parts: this needs to be done using just the Query 400 menu (and I don't think it's the newest version, there's no SQL options). I have no methods for opening a ODBC connection to the 400 system, nor do I even have access to the server itself other than the query screen.

I have thought about creating a macro in Attachmate's Extra Basic which would prompt the user for their user id, copy the base query to a temp query, hard code their user id, then run the query but I have no methods for deleting the "temp queries" as running the query interactively rather than in a batch will really bog down the system, and I don't want the Macro (and the user) to be kept in a "waiting" state until the batch report finishes just so the macro can delete it.

I have thought about creating one new query for each person who needs it, hard coding their user IDs into the query, but there are over 130 people who will be running this, and that's just too big a mess to make.

So are there any suggestions? Is there any function for returning the current user's ID similar to the current(date) query feature?

AC

 

[flapeyre]

No way to do it in Query directly, but you can work around it.

Here is a CL:

[tt]
PGM
DCL VAR(&USRPRF) TYPE(*CHAR) LEN(10)
RTVUSRPRF USRPRF(*CURRENT) RTNUSRPRF(&USRPRF)
DSPUSRPRF USRPRF(&USRPRF) OUTPUT(*OUTFILE) +
OUTFILE(QTEMP/PROFILE)
RUNQRY QRY(your_lib/your_query_name) +
QRYFILE((QTEMP/PROFILE) (FILEA))
ENDPGM
[/tt]

In the query, QTEMP/PROFILE is the first file, and you join it to FILEA (the file you have in your present query:

For Type of join, specify 1=Matched records. For the Specify how to join files fields, do this:

[tt]
Field Test Field
*ALL
[/tt]

When you select records, select T01.UPUPRF EQ <user_id_field>. That will give you the records for that person's User ID only.

The users will then run the CL instead of running the query directly.


&quot;When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return.&quot;

--Leonardo da Vinci

 

[arniec]

That sounds great...but I should have mentioned this. I'm not really allowed much access to the AS/400 server at all. It's in a different state and heavily guarded (possibly by people with machine guns, I'm not sure...but they give access to NO ONE). I have no access to standard AS/400 menus and prompts which, for this project, is frustrating. I can't even choose between RUNQRY and STRQMQRY (otherwise I'd just use STRQMQRY and have a macro specify the user id through an input prompt).

I'm stuck with just the standard query menu. I have options 1 through 9, but no others. Using this, is there any way to prompt users for a value? I'm thinking that having them type it again may be the only way to have this work at all, but without STRQMQRY I don't know how to make that work.

And flapeyre, thank you for your time, patience, and help. I am very much appreciative of it.

AC

 

[iSeriesCodePoet]

Then you should tell them this is about the only option there is. Short of a CL program, there is no value for 'logged on user' in WRKQRY. Is this an HR system that on this machine? If they are worried about you seeing too much, tell them to created you a view for you to use, then they can restrict what data you see.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

See my progress to converting to linux.

http://koldark.hopto.org/linux/

The Programmer's Knowledge Base ->

http://koldark.hopto.org/twiki/bin/view/Programkb/WebHome

 

[arniec]

Well there's no HR data, but they are extremely protective of their system. We are a very large company spread out over many states and the only people who are given access to the AS/400 system are an elite group of &quot;priests&quot;, who are reluctant to make any changes for anyone without a great offering.

I was hired as a sort of triage programmer, doing Access DBs, Attachmate Extra macros, Excel macros and spreadsheets, and AS/400 queries as well as anything else that comes up. Unfortunately this jack-of-all-trades job precludes me from being in the AS/400 department, so they will not give me one lick more access then they give the standard data entry users.

So...no way to even prompt? Because if I can't do this through programming it'll have to be done through policy, which is exceedingly error prone, but it may be what we have to do

AC

 

[iSeriesCodePoet]

Wow!! Our's, currently, is more open then a Las Vegas &quot;escort&quot; (Pardon the expression), of course we are working to change that. Still, if they don't want to allow you to get the job done right, that is their problem if it doen't work.

I would tell you project manager and/or manager and say, &quot;Hey, for this project here is the best solution I can come up with. It is totally secure and fail proof, but there is a problem. I can't implement this because I can't get on the system and create this program. I could do (second option here), but there is a security risk involved and more training for the users.&quot; If they are worried about security that much, let them know how secure it is. If you say &quot;this othere option is less secure&quot; they might just create you that program. ;-)

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

See my progress to converting to linux.

http://koldark.hopto.org/linux/

The Programmer's Knowledge Base ->

http://koldark.hopto.org/twiki/bin/view/Programkb/WebHome

 

[arniec]

Well my boss is non-technical. I'm not in an IT department, I'm a lone IT professional in a processing department. So it requires interdepartmental cooperation for my security levels to be increased. So far the quickest security increase has taken 2.5 weeks...so my boss chooses the less secure less accurate methods over interdepartmental cooperation. Plus it seems she feels she has a certain number of requests for the AS/400 gurus and she doesn't want to waste them.

 

[iSeriesCodePoet]

I would still go to her. If you clearly explain the issues, I would hope she would work with you to get it working.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

See my progress to converting to linux.

http://koldark.hopto.org/linux/

The Programmer's Knowledge Base ->

http://koldark.hopto.org/twiki/bin/view/Programkb/WebHome

 

[flapeyre]

Arniec:

Sorry I can't offer anything better, but IBM hasn't enhanced Query/400 in a long, long time and apparently isn't going to. The best I can offer you is to try to convince them that either (a) you need more access in order to get the job done or, (b) give them my CL program and let them implement it.

Sounds like their living room furniture still has the plastic covers on it, and nobody ever uses it.


&quot;When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return.&quot;

--Leonardo da Vinci