Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Query on DB2 table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
vickey_dw

New User


Joined: 10 Jun 2015
Posts: 30
Location: India

PostPosted: Wed Sep 02, 2015 5:41 pm    Post subject: Query on DB2 table
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: 1715
Location: UK

PostPosted: Wed Sep 02, 2015 6:35 pm    Post subject:
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: 1278
Location: Belgium

PostPosted: Wed Sep 02, 2015 6:37 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Wed Sep 02, 2015 8:11 pm    Post subject:
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: 331
Location: USA

PostPosted: Wed Sep 02, 2015 9:04 pm    Post subject: Reply to: Query on DB2 table
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: 30
Location: India

PostPosted: Thu Sep 03, 2015 11:06 am    Post subject:
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: 1278
Location: Belgium

PostPosted: Thu Sep 03, 2015 5:43 pm    Post subject:
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: 641
Location: Whitby, ON, Canada

PostPosted: Thu Sep 03, 2015 6:03 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Thu Sep 03, 2015 7:54 pm    Post subject:
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: 30
Location: India

PostPosted: Fri Sep 04, 2015 4:25 pm    Post subject:
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: 1715
Location: UK

PostPosted: Fri Sep 04, 2015 4:49 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us