MVSFORUMS.com

vmbigot

We have a production backup job that uses a LISTDEF parm to include all tablespaces in a database that need to have an image copy created. See below for an example:

kolusu · Posted: Fri Mar 30, 2007 12:33 pm Post subject:

vmbigot,

Can't you just issue "stop database" command before the image copy and restart them after you have finished the imagecopy?

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

vini_srcna · Beginner Joined: 17 Jul 2005 Posts: 9 Topics: 0

CHECK PENDING is a restricted state. If the tablespace is in CHKPND status it will not allow any DML issued against it.

I'm not sure, if the STOP DATABASE command can bring the tablspace back into normal state. I think you have to go for REPAIR or CHECK DATA utitlity which depends upon the importance of data. REPAIR would just reset the flag without checking the data.
_________________
Thanks,
Vinay Kumar,
IBM Certified DB2 UDB Database Administrator for Z/OS.
IBM Certified Application Developer for DB2 V8 Family.

videlord · Beginner Joined: 09 Dec 2004 Posts: 147 Topics: 19

Koluso,
IMAGE COPY is a DB2 online utility
Are you sure it can be run when tablespace in stopped mode?

And Stop and Start will not remove the CHKP status

vmbigot,
You should run check data utility first. If there are some RI confilist, you have to deal with it (may delete the inconsistant data)

Get the CHKP tablespace list by the following command:
-DIS DB(*) SP(*) RES(CHKP)

vmbigot · Posted: Mon Apr 02, 2007 4:45 pm Post subject:

I do thank everyone for their quick response to my post and your suggestions are very helpful.

However, the suggestions from you and IBM require manual intervention and repair before the backups run. These backup jobs run after hours with no operator intervention, so I am looking for a way to either bypass those tablespaces that have a check pending or other status that would cause an image copy to fail. So far the only way I can see to get around this is to create a script (using an example given in a reply to this post) that checks the status of all tablespaces in a database and outputs it to a flat file, edit that file and create EXCLUDE statements that would be concatenated to my SYSIN stream.

Do you agree or is there a better way?
_________________
Inside every older man is a young boy asking this question. What the heck happened?!?

vini_srcna · Beginner Joined: 17 Jul 2005 Posts: 9 Topics: 0

Firstly, Why do you decide to compromise with the restricted states..?
All you have got to do is, to know why the tablespaces are in pending status.
What jobs are running before your image copy jobs..?
If there is any RI and was there any load jobs ran on those tables..?
There could be N no of reasons. When u do a load on parent table all of the child tables will go to check pending status.
Check constraints defined on the table would be another reason.

I think you have some work here. Some times just taking the image copy will not do.
_________________
Thanks,
Vinay Kumar,
IBM Certified DB2 UDB Database Administrator for Z/OS.
IBM Certified Application Developer for DB2 V8 Family.

Manas Biswal · Posted: Wed Apr 11, 2007 9:35 am Post subject:

vmbigot,

I think what you essentially want is a way to skip over some items in a list when using LISTDEF. The ITEMERROR option will do that for you.

Here is the link -

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.ugref/cuoptns.htm

Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

vmbigot · Posted: Thu Apr 12, 2007 10:52 am Post subject:

Manas,

That is exactly what I was looking for. The ITEMERROR option worked perfectly.

In our test database environments, the programmers often leave their test tablespaces in a check pending status when they leave for the day. Our nightly batch cycle includes backups of all test databases and if one tablespace is left in a check pending status the backup would stop at that point, resulting in all tablespaces following it in the list to not get backed up. I would then have to log in, exclude the offending tablespace in the backup job, and then resubmit the backup job. This option alleviates the need to verify at the end of each day that developers have not left their test tables in odd states.

Again, thanks for your assistance!!! Mr. Green

vmbigot
_________________
Inside every older man is a young boy asking this question. What the heck happened?!?

dbzTHEdinosauer · Posted: Thu Apr 12, 2007 12:25 pm Post subject:

vmbigot,

have you

told the programmers their tablespaces will not be backed-up if a table is in check pending status
provided them with a utility or instructions to change the check-pending status?

_________________
Dick Brenholtz
American living in Varel, Germany

vmbigot · Posted: Thu Apr 12, 2007 3:21 pm Post subject:

Dick,

Yes to both. My developers are well aware that if they leave their tables in weird states that they will not be backed up. The first time they need to restore a table from the previous night's backup and they realize they don't have a valid copy will be a lesson well learned on the value of keeping things clean! Embarassed

Thanks,
vmbigot
_________________
Inside every older man is a young boy asking this question. What the heck happened?!?