HANA script - merge sql statements - syntax

[Former Member]

Hi

I'm trying to create a very simple HANA SQL statement which will delete backups in the catalog older than 30 days, and struggling to link the 2 original statements I wish to use due to syntax issues.  Will be so helpful if someone can assist with the HANA syntax to link this 2 statements!

I have the following:

This script will select the output of the a backup ID older than 30 days:

SELECT TOP 1 min(to_bigint(BACKUP_ID)) FROM SYS.M_BACKUP_CATALOG where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP, -30) and ENTRY_TYPE_NAME = 'complete data backup' and STATE_NAME = 'successful'

and in this script I wish to use the output of the top SQL:

BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID xxxxx COMPLETE

I can't get it to work by linking these 2 statements above, I tried by placing it in brackets, eg :  (will be great if this could work - merge into a single statement)

BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID ( SELECT TOP 1 min(to_bigint(BACKUP_ID)) FROM SYS.M_BACKUP_CATALOG where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP, -30) and ENTRY_TYPE_NAME = 'complete data backup' and STATE_NAME = 'successful' )  COMPLETE

also not by assigning a variable to it: SET 'MY_VAR'=select TOP 1 min(to_bigint(BACKUP_ID)) FROM SYS.M_BACKUP_CATALOG where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP, -27) and ENTRY_TYPE_NAME = 'complete data backup' and STATE_NAME = 'successful';

I managed to create a procedure:

create procedure BACKUPID_30

language SQLSCRIPT AS

BACKUPID integer;

begin

select TOP 1 min(to_bigint(BACKUP_ID)) FROM SYS.M_BACKUP_CATALOG where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP, -30) and ENTRY_TYPE_NAME = 'complete data backup' and STATE_NAME = 'successful';

end

but also struggling to assign the output of the procedure to a variable: SET 'MY_VAR'=CALL "BACKUPID_27"

Will someone please advise on this merge?

Thanks

[lbreddemann · Accepted solution]

Hmm... not sure where you struggled, but it's actually straight forward:

do
begin
declare back_id BIGINT =0;
declare cmd NVARCHAR(2000);


-- find the backup id we want to base the deletion on:
  SELECT
    min(to_bigint(BACKUP_ID)) into back_id
  FROM SYS.M_BACKUP_CATALOG
  where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP, -90)
  and ENTRY_TYPE_NAME = 'complete data backup'
  and STATE_NAME = 'successful';

-- build the delete statement 
   cmd := 'BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID ' || :back_id || ' COMPLETE';

-- run the statement we just built
   execute immediate (:cmd);


end;

[Former Member · Accepted solution]

Tested and working and happiness, thanks!

[Former Member · Accepted solution]

some feedback,

to schedule the above select statements in a hdbsql script for crontab proved very challenging, because of the complexity of the statements, errors were:

> hdbsql -U BACKUP -I catalog_clear -o catalog_out

* 257: sql syntax error: line 4 col 25 (at pos 70) SQLSTATE: HY000

* 257: sql syntax error: incorrect syntax near "declare": line 1 col 1 (at pos 1) SQLSTATE: HY000

* 337: INTO clause not allowed for this SELECT statement: line 3 col 31 (at pos 98) SQLSTATE: HY000

* 257: sql syntax error: invalid SQL type SQLSTATE: HY000

* 257: sql syntax error: incorrect syntax near "cmd": line 1 col 1 (at pos 1) SQLSTATE: HY000

* 257: sql syntax error: incorrect syntax near "execute": line 2 col 4 (at pos 41) SQLSTATE: HY000

* 257: sql syntax error: incorrect syntax near "end": line 1 col 1 (at pos 1) SQLSTATE: HY000

I reverted to the Shell Script from https://scn.sap.com/thread/3604561 which worked very well for me:

#### Start Shell Script:

### go to the hdbclient install directory.
cd /HANA/backups/scripts/catalog

### Get min full backup id in the last 45 days
hdbsql -U BACKUP -o backupid.txt -x -a "SELECT TOP 1 min(to_bigint(BACKUP_ID)) FROM SYS.M_BACKUP_CATALOG where SYS_START_TIME >= ADD_DAYS(CURRENT_TIMESTAMP, -45) and ENTRY_TYPE_NAME = 'complete data backup' and STATE_NAME = 'successful'"

### read the backup id from a file and assign to variable $BACKUPID
read BACKUPID < backupid.txt

### Create a file containing a query to prune the backup catalog and files before the identified backup id
cat >  catalogdel.txt << EOF
BACKUP CATALOG DELETE ALL BEFORE BACKUP_ID $BACKUPID COMPLETE
EOF

### Read the query from the file and
hdbsql -U BACKUP -I catalogdel.txt

[lbreddemann · Accepted solution]

Well, you cannot run SQLScript code directly in hdbsql. I haven't tried to use the new (as of SPS 10) DO BEGIN clause with hdbsql, but looking at the error codes you posted, this seems to be the issue here.

Not sure why you don't simply create a stored procedure with the code I showed and CALL this procedure from hdbsql. Anyhow, the shell script variant works fine too, so there you go