We are moving DB's from v11 to v17 and backing up each DB to NAS

At first the backups using the drive letter wouldn't work, so we used the full name and it worked - now it's started to kick off again...

BEGIN 
DECLARE day_of_week VARCHAR(9); 
DECLARE backup_stmt LONG VARCHAR; 
SET day_of_week = substr(dayname(today()),1,3); 
SET backup_stmt = 'BACKUP DATABASE DIRECTORY '|| 
                  '''\\\\\Storeage003\\DB2\\BACKUP\\CrescentVillasCA17\\' || day_of_week || ''' ';
EXECUTE IMMEDIATE backup_stmt;
END

and throws the toys out with

Could not execute statement.
Error during backup/restore: Could not create directory
"\\Storeage003\DB2\DB2\BACKUP\CrescentVillasCA17\Sun" -- No such file or directory
SQLCODE = =-697, ODBC 3 State='HY000"
Line 1, column 1

Any ideas?

I am aware that the NAS name is a typo, but it is correct nonetheless.

Thanks

asked 15 Jan, 11:40

gchq's gravatar image

gchq
1814819
accept rate: 33%

Is there a deliberately doubled "DB2" in the path? It does not appear in your code sample...

(15 Jan, 15:48) Volker Barth
Replies hidden

Mea Culpa - having one of 'those' days - only one DB2

(15 Jan, 16:42) gchq

They have come a long way with health care, but there is still no cure for stupid!

The ones that were working also had the share name! Sigh...

BEGIN 
DECLARE day_of_week VARCHAR(9); 
DECLARE backup_stmt LONG VARCHAR; 
SET day_of_week = substr(dayname(today()),1,3); 
SET backup_stmt = 'BACKUP DATABASE DIRECTORY '|| 
                  '''\\\\\Storeage003\\DB_Backups\\DB2\\BACKUP\\ParkwoodNV17\\' || day_of_week || ''' ';
EXECUTE IMMEDIATE backup_stmt;
END

Sorry :-(

permanent link

answered 15 Jan, 19:48

gchq's gravatar image

gchq
1814819
accept rate: 33%

Glad you fixed it.

FWIW, you can use EXECUTE IMMEDIATE with the ESCAPES OFF clause - that should make it easier to handle all those quadruple backslashes...

(16 Jan, 02:42) Volker Barth

Also FWIW, I've found that (at least with SA17) I can use forward slashes, even under Windows, and thereby avoid all the 'escaping' (don't know if this is documented or not). This works well for me because I can then deploy to both Windows and Linux (where the forward slashes are required).

(16 Jan, 10:52) Terry Wilkinson

Two useful bits of information :-)

Thank you

(16 Jan, 10:54) gchq
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×72

question asked: 15 Jan, 11:40

question was seen: 304 times

last updated: 16 Jan, 10:54