467,201 Members | 1,055 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,201 developers. It's quick & easy.

SQL stmt to find if table is in load pending state?

How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)

Apr 12 '06 #1
  • viewed: 4657
Share:
10 Replies
Check this in SYSTEM MONITOR GUIDE

tablespace_state_change_object_id - State Change Object Identification
monitor element
Cheers...
Shashi Mannepalli
technocrat wrote:
How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)


Apr 12 '06 #2
Do a Google search on: db2 and snapshot table fubnctions
You get to:
http://publib.boulder.ibm.com/infoce...n/t0010418.htm
The URL may have wrapped in this answer.

Look at the SNAPSHOT_TBS function. it returns, amongst other things, the
state of the tblsp.
Given it's a table function, you can use it as SQL.
HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"technocrat" <su************@gmail.com> a écrit dans le message de news:
11**********************@j33g2000cwa.googlegroups. com...
How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)


Apr 13 '06 #3
OOOPS!
My apologies but the snapshot_tbs table function will not return the table
space state.
Mes excuses, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"technocrat" <su************@gmail.com> a écrit dans le message de news:
11**********************@j33g2000cwa.googlegroups. com...
How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)


Apr 13 '06 #4
i am actually trying to use the sysproc.db2load as a callable statement
and it returns some 6 OUT parameters...one of them is sqlerrorcode....
how can i actually access the value of that paramenter...like ..the
value of the 4th OUT parameter of the callable statemet...
any help appreciated..!

Apr 14 '06 #5
ok i figured out the above one but now my doubt is once the load failed
i want to run the load with terminate command. so i will be calling
CREATE PROCEDURE SYSPROC.DB2LOAD
so in the above callable statement what would be the secnd parameter (
the select cusor ) because my load st will be "load from /dev/null of
del terminate into control.ACCOUNT nonrecoverable" so there is no
select statement, how would i do this???/?

Apr 14 '06 #6
Correct me if i am wrong.

Just using:
select tabname, status from syscat.tables where tabname=' xxx',
from status, you can tell what state the table is.
Mike

technocrat wrote:
ok i figured out the above one but now my doubt is once the load failed
i want to run the load with terminate command. so i will be calling
CREATE PROCEDURE SYSPROC.DB2LOAD
so in the above callable statement what would be the secnd parameter (
the select cusor ) because my load st will be "load from /dev/null of
del terminate into control.ACCOUNT nonrecoverable" so there is no
select statement, how would i do this???/?


Apr 14 '06 #7
mike_li wrote:
Correct me if i am wrong.

Just using:
select tabname, status from syscat.tables where tabname=' xxx',
from status, you can tell what state the table is.
Mike

technocrat wrote:
ok i figured out the above one but now my doubt is once the load failed
i want to run the load with terminate command. so i will be calling
CREATE PROCEDURE SYSPROC.DB2LOAD
so in the above callable statement what would be the secnd parameter (
the select cusor ) because my load st will be "load from /dev/null of
del terminate into control.ACCOUNT nonrecoverable" so there is no
select statement, how would i do this???/?


Unfortunately no ... the status field indicates the check pending status
and is not related to the LOAD status:
The check pending status of the object:

* N = Normal table, view, alias or nickname
* C = Check pending on table or nickname
* X = Inoperative view or nickname
Bob
Apr 14 '06 #8
Pierre Saint-Jacques wrote:
OOOPS!
My apologies but the snapshot_tbs table function will not return the
table space state.
Mes excuses, Pierre.

Pierre - It seems the snapshot_tbs_cfg table function is what you want here?
Apr 14 '06 #9
So finally whats the solution????? snapshot _tbs_cfg? I have no idea
how that should be implemented..any suggestons..I will google also...

Apr 14 '06 #10
As Bob [IBM] pointsout further, I used the wrong function:
This is the one you want: snapshot_tbs_cfg
It provides column:
TABLESPACE_STATE
The following link explains the retrun code values:
http://publib.boulder.ibm.com/infoce...n/t0010418.htm

HTH, Pierre.
PS - See what Google can do for you? With help from people to set me right,
I can find stuff!
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Pierre Saint-Jacques" <se*****@invalid.net> a écrit dans le message de
news: gE**********************@wagner.videotron.net...
OOOPS!
My apologies but the snapshot_tbs table function will not return the table
space state.
Mes excuses, Pierre.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"technocrat" <su************@gmail.com> a écrit dans le message de news:
11**********************@j33g2000cwa.googlegroups. com...
How can i find the load state of the table using SQL, i can do "load
query table tablename" but i cannot execute this as a sql statement.
What i want to do is, i m using a java stored priocedure to load a
table, now after loading, i want to make sure load completed
successfully by checking the state of the table
like
if(tablestate.equals("N"))
{proceed forward}
else
break here..
Any suggestions are welcome...
PS
Pierre, since u have answered my previous 2 questions , I assume you ar
egoing to do for this one too..:-)


Apr 15 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by R. Clausen | last post: by
reply views Thread by GB | last post: by
7 posts views Thread by satish mullapudi | last post: by
4 posts views Thread by Mr. DOS | last post: by
1 post views Thread by amitabh.mehra@gmail.com | last post: by
1 post views Thread by balubkp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.