db2 del script help please

[nbritton]

I am trying to create a script that will delete db with a keep x copies, and will also delete un-neededed logs. I have tried to use the scirpt from the redbook (pasted below) but i get the following error. Can someone help me with this?

Error. 'S-0000001.LOG' is not a valid log file name.

#!/bin/ksh
set -x
DB=SAMPLE
KEEP_FULL=5
#
# Delete full DB backups and keep $KEEP_FULL
#
db2adutl delete full keep $KEEP_FULL db $DB without prompting
#
# This is the tricky part to determine the oldest logfilenumber
# We assume that the last image in the query output has the oldest log number
# We had to subtracte one from the oldest logfile in order to keep the
# last backup valid.
#
let OLDEST_LOG=$(db2adutl query full db $DB | tail -2 | awk '{print $3}' | cut
-c2-8 )
let OLDEST_LOG=$OLDEST_LOG-1
OLD_LOG=$(printf "%-7.7d" $OLDEST_LOG)
OLD_LOG="S$OLD_LOG.LOG"
# Now delete obsolete logs
#
db2adutl delete logs between S0000000.LOG and $OLD_LOG db $DB without prompting

 

[alimirza]

Since you have 'set -x' in the script, can you paste the output generated when you run this script. This will shed some light on the issue

 

[nbritton]

output from script

$ ./deldbbackups.sh
+ DB=db2test
+ KEEP_FULL=4
+ db2adutl delete full keep 4 db db2test without prompting

Query for database DB2TEST


Retrieving FULL DATABASE BACKUP information.
Taken at: 20070208121230 DB Partition Number: 0 Sessions: 1


The current delete transaction failed. You do not have
sufficient authorization. Attempting to deactivate
backup image(s) instead...

Success.



Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP images found for DB2TEST


Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP images found for DB2TEST

+ db2adutl query full db db2test
+ tail -2
+ awk {print $3}
+ cut -c2-8
+ let OLDEST_LOG=ATABASE
+ let OLDEST_LOG=0-1
+ printf %-7.7d -1
+ OLD_LOG=-0000001
+ OLD_LOG=S-0000001.LOG
+ db2adutl delete logs between s0000000.LOG and S-0000001.LOG db db2test without prompting


Error. 'S-0000001.LOG' is not a valid log file name.

Usage: db2adutl

{ QUERY [ [ { [ LOADCOPY ] |
[ TABLESPACE | FULL] [ NONINCREMENTAL | INCREMENTAL | DELTA ] }
[ SHOW INACTIVE ] ] |
[ LOGS [ BETWEEN <Sn1> AND <Sn2> ] [ CHAIN <n> ] ] ] |

EXTRACT [ [ { [ LOADCOPY ] |
[ TABLESPACE | FULL] [ NONINCREMENTAL | INCREMENTAL | DELTA ] }
[ SHOW INACTIVE ]
[ SUBSET ] |
[ TAKEN AT <timestamp> ] ] |
[ LOGS [ BETWEEN <Sn1> AND <Sn2> ] [ CHAIN <n> ] ] ] |

DELETE [ [ { [ LOADCOPY ] |
[ TABLESPACE | FULL] [ NONINCREMENTAL | INCREMENTAL | DELTA ] }
[ KEEP <n> |
OLDER [THAN] [ <timestamp> |
<n> DAYS ] |
TAKEN AT <timestamp> ] ] |
[ LOGS [ BETWEEN <Sn1> AND <Sn2> ] [ CHAIN <n> ] ] ] |

VERIFY [ [ verify-options ]
{ [ LOADCOPY ] |
[ TABLESPACE | FULL ] [ NONINCREMENTAL | INCREMENTAL | DELTA ] }
[ SHOW INACTIVE ]
[ TAKEN AT <timestamp> ] ] |

[ COMPRLIB <decompressionLibrary> ]
[ COMPROPTS <decompressionOptions> ]

[ VERBOSE ]

[ {DATABASE | DB} <database name> ]
[ DBPARTITIONNUM <n> ]
[ PASSWORD <password> ]
[ NODENAME <nodename> ]
[ OWNER <owner> ]
[ WITHOUT PROMPTING ]

VERBOSE - displays extra file information

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

verify-options:

{ ALL | CHECK | DMS | HEADER | LFH | TABLESPACES | SGF | HEADERONLY |
TABLESPACESONLY | SGFONLY | OBJECT | PAGECOUNT }

ALL - Display all of the information we can.
CHECK - Display the results from the page checking.
DMS - Display information on DMS tablespace data.
HEADER - Display the media header information.
LFH - Display the LFH (Log File Header) and MFH (Mirror LFH) data.
TABLESPACES - Display tablespace details for those in the image.
SGF - Display the automatic storage paths in the image.
HEADERONLY - ONLY display the media header information.
TABLESPACESONLY - ONLY display tablespace details for those in the image.
SGFONLY - ONLY display the automatic storage paths in the image.
OBJECT - Display detailed information from the objects.
PAGECOUNT - Display the number of pages of each object type.

NOTE:
The HEADERONLY, TABLESPACESONLY and SGFONLY option do NOT
validate the image.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

TSM cross-node access control:

GRANT { ALL | USER <username> } ON { ALL | NODENAME <nodename> }
FOR { DATABASE | DB } <database name>
REVOKE { ALL | USER <username> } ON { ALL | NODENAME <nodename> }
FOR { ALL | { DATABASE | DB } <database name> }
QUERYACCESS FOR { ALL | { DATABASE | DB } <database name> }

[ PASSWORD <password> ]

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

To extract pages, you will need an input and output file.
The default input file is called extractPage.in. To override this
set the environment variable DB2LISTFILE to a full path.

The format of the input file is as follows:
For SMS: S <tbspID> <objID> <objType> <startPage> <numPages>
For DMS: D <tbspID> [<objType>] <startPage> <numPages>
NOTE: <objType> is only needed if verifying DMS load copy images
For log files:
L <log num> <startPos> <numPages>
For other data (i.e. initial data):
O <objType> <startPos> <numBytes>
where each entry is one per line.

The output will be put into extractPage.out by default. To override
this set the environment variable DB2EXTRACTFILE to a full path.

 

[alimirza]

Hi, If you can put an "echo $OLDEST_LOG" and "echo $OLDLOG" to see what value is returned in each step.

Also to determine the name of the logs that you have stored on TSM server, look at the output of follwing two queries (I am not sure if the logs are in archives or backup table and therefore if you can run the query against each table).

dsmadmc> select ll_name,hl_name,backup_date from backups where node_name="NODE_NAME"

dsmadmc> select ll_name,hl_name,archive_date from archives where node_name="NODE_NAME"

Replace the nodename with the actual node name of this node. What I am trying to see is the stored LOG name on TSM server and is the script getting this LOGname correcty.

 

[nbritton]

output from echo

++ db2adutl query full db db2test
++ tail -2
++ awk '{print $3}'
++ cut -c2-8
+ let OLDEST_LOG=ATABASE
+ echo OLDEST_LOG is 0
OLDEST_LOG is 0
+ let OLDEST_LOG=0-1
+ echo OLDEST_LOG-1 is -1
OLDEST_LOG-1 is -1
++ printf %-7.7d -1
+ OLD_LOG=-0000001
+ echo OLD_LOG is -0000001
OLD_LOG is -0000001
+ OLD_LOG=S-0000001.LOG
+ echo OLD_LOG is S-0000001.LOG
OLD_LOG is S-0000001.LOG
+ echo db2test
db2test
+ db2adutl delete logs between S0000000.LOG and S-0000001.LOG db db2test without prompting


Would it be getting a negitive one becosue 1-0 is -1 and that would cause the problem.

 

[alimirza]

Hi,
It seems that the alignment of the 'db2adutl query full db db2test' has changed and from your output we can see that the first OLDEST_LOG is some how getting ATABASE as the vaule. The value shoud be a 7 digit number like (0000047) which is the olddest log that you have. So can you simply run the following command and paste the output here.

db2adutl query full db db2test

Based on the output we will need to adjust the script so that we can extract the correct info.

 

[nbritton]

output from query

$ db2adutl query full db db2test


Query for database DB2TEST


Retrieving FULL DATABASE BACKUP information.
1 Time: 20070208153919 Oldest log: S0000004.LOG DB Partition Number: 0 Sessions: 1
2 Time: 20070208134850 Oldest log: S0000003.LOG DB Partition Number: 0 Sessions: 1
3 Time: 20070208133126 Oldest log: S0000002.LOG DB Partition Number: 0 Sessions: 1
4 Time: 20070208122751 Oldest log: S0000001.LOG DB Partition Number: 0 Sessions: 1


Retrieving INCREMENTAL DATABASE BACKUP information.
No INCREMENTAL DATABASE BACKUP images found for DB2TEST


Retrieving DELTA DATABASE BACKUP information.
No DELTA DATABASE BACKUP images found for DB2TEST

 

[alimirza]

Hi, the output from your 'db2adutl query full db db2test' confirms that the output in the book is different then what you are getting. You need to adjust the script. In the script where it says 'tail -2' you need to replace it with 'tail -x' (where x is the line number that gives you following line as output).

4 Time: 20070208122751 Oldest log: S0000001.LOG DB Partition Number: 0 Sessions: 1

I beleive the value of -x in the tail command should be 9 but you need to test it and use the correct number to get the output.

Also where it says awk '{print $3}' this should be replaced with awk '{print $6}' (as its sixth column in the output that has the log number).

So, first test the result by running the commands in the script on the unix prompt to confirm the result and then use the script with the adjusted variables.

Regards

 

[esiole]

+ db2adutl query full db db2test
+ tail -2
+ awk {print $3}
+ cut -c2-8
+ let OLDEST_LOG=ATABASE

<== As you can see, it is just the db2adutl command that provide wrong OLDEST_LOG variable (due to the "tail -2").

So just replace :

db2adutl query full db DB2SID|tail -2 |awk '{print $3}' | cut -c2-8
with
db2adutl query full db DB2SID|grep "Sessions:" |awk '{print $3}' | cut -c2-8

And it will work perfectly by putting the good variable in place.

Results :
0080617
0080616
0080615 and so one.....

Regards

 

[Bartdo]

db2 delete script

If you are still getting the message
>> The current delete transaction failed. You do not have
>> sufficient authorization. Attempting to deactivate backup image(s) instead

it is because you have not allowed BACKUP DELETE for the TSM node.
to fix, do this: update node tsmnode backdel=yes

rgds,
Bart