Backup using backup_exec_cmd

[robert_kratschmann]

Hi, using the IQ demo database I executed the following statements on IQ 16.0 SP11 PL3. The iqdemo database consists of iqdemo.db, iqdemo.log, iqdemo.iq and iqdemo_main.iq.

After setting the temporary options the BACKUP DATABASE statement will generate copies of the *.iq files via dd. Finally an INCREMENTAL SINCE FULL backup is necessary:

connect using 'uid=DBA;pwd=sql;eng=HOST_iqdemo';

set temporary option virtual_backup='ON';

set temporary option backup_exec_cmd='dd if=iqdemo.iq of=iqdemo.iq.copy;dd if=iqdemo_main.iq of=iqdemo_main.iq.copy';

BACKUP DATABASE FULL TO 'iqdemo.full';

drop table if exists t1;

create table t1 (c1 int, c2 varchar(20));

insert into t1 values (1,'AAA');

BACKUP DATABASE INCREMENTAL SINCE FULL TO 'iqdemo.isf';

Now stop IQ, remove all files which are necessary for the iqdemo database, and restart the utility database:

--$ stop_iq -stop all

--$ rm iqdemo.iq

--$ rm iqdemo_main.iq

--$ rm iqdemo.db

--$ rm iqdemo.log

--$ start_iq -n utilitydb

After the connection with the uiltity database do a RESTORE:

connect using 'uid=DBA;pwd=sql;eng=utilitydb;dbn=utility_db';

RESTORE DATABASE 'iqdemo' FROM 'iqdemo.full';

At this point I found that the dbspace files iqdemo.iq and iqdemo_main.iq exists (They have been removed at O/S level above)!!

RESTORE DATABASE 'iqdemo' FROM 'iqdemo.isf';

--$ stop_iq -stop all

--$ start_iq @iqdemo.cfg iqdemo.db

connect using 'uid=DBA;pwd=sql;eng=HOST_iqdemo';

select * from t1;

-- 1 row result OK

So my question is: Why is it not necessary to recreate the dbspace files? If I do a BACKUP DATABASE FULL VIRTUAL ENCAPSULATED instead of backup_exec_cmd I had to recreate this files in a fist step using two dd's ...

Many thanks

Robert

[robert_kratschmann · Accepted solution]

Hi Mark and Jerry thanks for the info.

I found the syntax in the official Training for IQ 16 (EDB785 - SAP IQ Administration) - Unit 16, Lesson 1, Backing Up SAP IQ databases on the Pages 791 and 792. A comment that it is viable only in 12.x is missing. So I did the test with the demo database.

The interesting thing is that backup_exec_cmd is working: I did a test with backup_exec_cmd and "BACKUP DATABASE VIRTUAL ENCAPSULATED <shellscript>". Both versions generate exactly the same size for the backup file (6.1MB), a normal full backup ("BACKUP DATABASE FULL TO ...") has a size of 51.3MB. So from my point of view the data is not included into the backup generated with backup_exec_cmd.

I checked the following:

1) The backup defined by backup_exec_cmd='dd if=iqdemo.iq of=iqdemo.iq.copy;dd if=iqdemo_main.iq of=iqdemo_main.iq.copy' is generated correct. The *.copy files have the same size as the *.iq device files.

2) The *.iq device files are deleted correctly - “ls *.iq” is not showing them.

3) A VM reboot between the deletion of the device files and the RESTORE command didn't change the behavior.

Maybe it's a behavior of the virtual Linux Server: The *.iq device files are marked as deleted and with the RESTORE the deletion mark disappear? As long as the space will not be used the data in the deleted files is not overwritten.

Robert

[markmumy · Accepted solution]

I will address the training material bug with our education staff and have it removed.  That is certainly very old, and very unsupported syntax that had a life for just 1 minor version of IQ.  Anything you see with those options and commands is unsupported, undocumented (per the manuals) and will lead to unexpected behavior in IQ.

Please focus solely on the BACKUP command with the VIRTUAL option(s) as this is the only supported backup process (virtual or otherwise) for IQ.

Side note, I was mistaken on when this syntax was removed.  We introduced the option syntax in IQ 12.5 and removed it in IQ 12.6 when we had a chance to integrate it into the BACKUP syntax.

Chapter 1: New Features in Sybase IQ 12.6

BACKUP syntax extended (behavior change)

The BACKUP statement now supports virtual backup with the two new parameters VIRTUAL DECOUPLEDand VIRTUAL ENCAPSULATED. The new parameters perform the functions of the database optionsVIRTUAL_BACKUP and BACKUP_EXEC_CMD, which have been deprecated.

Mark

[markmumy]

Can you let me know when you took the IQ class?  We are trying to run down where our old syntax is.  Right now, though, the current classes don't show this syntax from what I've seen so far.

Mark

[jong-kil_park]

Hi Robert,

I think the whole backup operation was not processed in a way you expected.

My thought is that,

     1) setting the temporary option virtual_backup and backup_exec_cmd were ignored as Mark said.

     2) the database was backed up in 'iqdemo.full' by 'backup database full' command. That's why the dbspace files exist just after completing the restore database command even though you didn't recreate them.

You had better rerun the scripts once again and check the following things.

      1) the size of the 'iqdemo.full' file. To be sure, you had better insert more data.

      2) whether the *.copy files are created or not.

Best regards,

Jerry

[markmumy]

Robert,

The backup_exec_cmd and virtual_backup option were deprecated more than 10 years ago and were only viable for the IQ 12.x product line.  The options aren't in the IQ 15 or 16 manuals so I'm curious where they came from.

In IQ, you can set any option you wish, we don't stop that.  The code behind those options no longer exists as it was ported to the BACKUP command so they are, in effect, a non operation.

The only approved virtual backup is via the BACKUP command and the VIRTUAL syntax to it. 

Basically, you are using options that are ignored by IQ due to being deprecated after IQ 12.7.  This will drive your backup to be a full backup so that upon restore the main files are recreated.

Basically, you aren't actually doing a virtual backup.  You should see this manifest itself in the size of the backup output.  A full backup will be much larger as it contains IQ data while a virtual backup only contains the catalog and a few other small things.

Hope this helps.

Mark