MVSFORUMS.com

[prog_mario]

Hi everybody,
I need to retrieve table name, plan/package, and, which actions that plan/package runs against the table (insert, update, select, delete, etc), from SYSIBM tables.
Can anyone give me some help on which tables I need to join to obtain that information?
Thanks
_________________
The more I learn, the more I want to learn.

[kolusu]

prog_mario,

I would start with

SYSIBM.SYSPACKAGE table for package and table name which is stored in QUALIFIER column
SYSIBM.SYSPACKSTMT table for the actual statement which is stored in the column STMT

[prog_mario]

Hi Kolusu,
I took a look at both tables but I still didn't find the columns I need.
The information I need is based on the PA (package authority) that we can check using BMC utility:

[kolusu]

prog_mario,

Did you check the STMT column in SYSIBM.SYSPACKSTMT table which stores the actual sql?

Kolusu

[prog_mario]

Yes,
with HEX OFF
Command ===> Scroll ===> CSR
STMT [code:1:342c208f7e]
---------+---------+---------+---------+---------+---------+---------+---------+
.................+..&...................
.................+..&...................
.................+..&...................
.................+..&...................
.......M...<......|..
_________________
The more I learn, the more I want to learn.

[kolusu]

prog_mario,

The data from sysibm tables is is stored in UNICODE UTF-8 format. In order to view it in EBCDIC format you need to use the ISPF DISPLAY command

Once you get the results from the query in spufi, simply enter the DISPLAY UTF8 command at the command prompt and hit enter.

Now to get back to normal EBCDIC form enter the command DISPLAY EBCDIC

Kolusu

[prog_mario]

I getting an error "Terminal CCSID not set" when I try to display UTF8.
_________________
The more I learn, the more I want to learn.

[kolusu]

prog_mario,

Time to talk to your DBA/systems programmer

Kolusu

[prog_mario]

The error occurs only when I'm connected remotely. When I use a VPN it works, but, STMT doesn't have the information I need.
_________________
The more I learn, the more I want to learn.

[CZerfas]

To retrieve the sorts of SQL statements a package is issuing against a table, use this query:
SELECT *
FROM SYSIBM.SYSTABAUTH
WHERE COLLID = 'your collection of the package'
AND GRANTEE = 'name of the program'
;

regards
Christian