IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Query on DB2 table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 51
Location: India

PostPosted: Wed Sep 02, 2015 5:41 pm
Reply with quote

Hello All

Three months back i have joined one banking account and i am looking into its Human resourse application.In this application we have tables related to employee information.

Most of the tables here are loaded using DB2 load utility by the data received from upstream system and other tables are populated by insert statement in source code.

I am able to find all the tables from where its getting data and how its loaded/inserted except one.

For Employe salary table i am not able to trace how its populated.I have searched in all jobs,source,procs,control cards by table name but still i am not able to find.Also this table is not getting populated from other applications outside mainframe.


Also its not like the table is not active, i have checked the TS its updated daily.


Please suggest some pointers where can i look to check how the table is populated.


Your suggestion/guidance would be much appreciated.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Wed Sep 02, 2015 6:35 pm
Reply with quote

Other team members, DBAs.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Sep 02, 2015 6:37 pm
Reply with quote

if it's static SQL (insert) you should be able to find it in SYSIBM.SYSPACKAGEDEP

if it's populated by a utility you should see it in SYSIBM.SYSCOPY . You still wouldn't know where but you would know it's loaded.

if it's dynamic SQL, you're S.O.L.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Wed Sep 02, 2015 8:11 pm
Reply with quote

Quote:
For Employe salary table i am not able to trace how its populated.I have searched in all jobs,source,procs,control cards by table name but still i am not able to find.Also this table is not getting populated from other applications outside mainframe.


I am sure you have not done the deep search here, have you checked SP,DB2 Trigger/functions,MQ's/CICS, Web Services or any monthly jobs, DB2 Logs or check for any online screens and at last but not the least, almost every table has the userid and timestamp so have you checked that to investigate further?
Back to top
View user's profile Send private message
RahulG31

Active User


Joined: 20 Dec 2014
Posts: 446
Location: USA

PostPosted: Wed Sep 02, 2015 9:04 pm
Reply with quote

If it is a reference only table i.e. the values are static in nature then most probably it is loaded with some adhoc requests.

I was once in a project where they used to load such table with a file generated from a code which was not in production. The code keeps on changing depending on the requirement to create new entries in table. So, they used to execute the program in test environment, create a file, load test tables and validate. If validation is fine then, load the production table with the same test file.

I am saying this since you must be searching the production libraries and the code which inserts into the table may Not be in production.

This may be a possibility.
Back to top
View user's profile Send private message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 51
Location: India

PostPosted: Thu Sep 03, 2015 11:06 am
Reply with quote

Thank you guys for your valuable suggesting and sharing your experience.

But believe me i have done a deep search multiple times in all prod libraries but still coulnot get any clue.

Dynaic SQLs are also not used here.

GuyC

I have tried your suggestion

Code:
Select * from  SYSIBM.SYSPACKAGEDEP
where tbname = 'sal table name'


But getting below error message

Code:
 SYSIBM.SYSPACKAGEDEP  couldnot be found


And similar for SYSIBM.SYSCOPY in both test and prod regions.

Hope i am using them correctly
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Sep 03, 2015 5:43 pm
Reply with quote

My mistake:

DB2 zOS => SYSIBM.SYSPACKDEP
DB2 luw => SYSIBM.SYSPLANDEP
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada

PostPosted: Thu Sep 03, 2015 6:03 pm
Reply with quote

I would also suggest looking at SYSIBM.SYSTABAUTH. Any package that updates the table will have a row. Unless the package uses dynamic SQL of course, in which case, as Guy put it so eloquently, you are S.O.L.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3049
Location: NYC,USA

PostPosted: Thu Sep 03, 2015 7:54 pm
Reply with quote

Vickey,

I may suggest you to get your DBA's in loop and tell them about the situation and homework that you did so far while getting answers here and then they may tell you what you have missed in your reasearch.

But you still did not answer my question, Does table have no userid and timestamp column?
Back to top
View user's profile Send private message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 51
Location: India

PostPosted: Fri Sep 04, 2015 4:25 pm
Reply with quote

Thank you guys for your valuable suggestions.

Hi GuyC and Don

I am using below query

Code:
SELECT * FROM SYSIBM.SYSPACKDEP WHERE BNAME = 'Table_Name'


But i am getting empty row(in prod).and same for SYSIBM.SYSCOPY & SYSIBM.SYSTABAUTH.
But for other tables as well i am getting empty rows.Please correct me if i have used incorrectly.

Hi Rohit
I have rechecked no userid and timestamp column.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Fri Sep 04, 2015 4:49 pm
Reply with quote

Quote:
Thank you guys for your valuable suggestions.

So what did your DBA say?
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts RC query -Time column CA Products 3
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
Search our Forums:

Back to Top