SAP Sybase IQ: How to Restore Your Backups to Another system

SAP/Sybase’s documentation isn’t very clear for new IQ dbas and developers. Once such item is sdbply restoring an IQ database on to another system. Unlike ASE, you need to specify the new file locations if they are different than the source server.

Assumptions:

  1. IQ software has been installed
  2. The new dbfile locations are symbolic links to raw partitions OR the path exists but not the files
  3. You have a valid SYSAM license for the new IQ instance.
  4. The new IQ instance name is set (via -n instance)
  5. The old directory for the .db, .log and .mir exists (use a symbolic link if you wish)

Obtain dbspace file names with sp_iqfile:

select DBFileName, Path, DBFileSize from sp_iqfile();
DBFileName	Path	DBFileSize
'IQ_SYSTEM_MAIN'	'/database/old_iq/devices/IQ_MAIN/old_iqmain001.iq'	'32G'
'IQ_USER_MAIN_FILE_01'	'/database/old_iq/devices/IQ_USER_MAIN/old_iqusermain001.iq'	'1024G'
'IQ_SYSTEM_TEMP'	'/database/old_iq/devices/IQ_TEMP/old_iqtemp001.iqtmp'	'32G'
'IQ_SYSTEM_TEMP_002'	'/database/old_iq/devices/IQ_TEMP/old_iqtemp002.iqtmp'	'32G'</code></pre>

Create a restore.sql file renaming the DBFileName to the new locations:
<pre class="prettyprint" ><code class="language-sql">restore database 'new_iq'
FROM '/backups/old_iq.20140423100111.17760.IQfullbkp'
RENAME IQ_SYSTEM_MAIN TO '/database/new_iq/devices/IQ_MAIN/new_iqmain001.iq'
RENAME IQ_SYSTEM_TEMP TO '/database/new_iq/devices/IQ_TEMP/new_iqtemp001.iq'
RENAME IQ_SYSTEM_TEMP_002 TO '/backups/new_iqtemp002.iq'
RENAME IQ_SYSTEM_MSG TO '/database/new_iq/IQLOG/new_iq.iqmsg'
RENAME IQ_USER_MAIN_FILE_01 TO '/database/new_iq/devices/IQ_USER_MAIN/new_iqusermain001.iq';
<p>Stop the destination IQ instance if it is running and start the utility database:</p> <pre class="brush: bash; title: ; notranslate" title="">stop_iq
Checking system ...

The following 1 server(s) are owned by 'sybdba'

## Owner          PID   Started  CPU Tdbe  Additional Information
-- ---------  -------  --------  --------  ------------------------------------
1: sybdba       13909     Apr24  00:43:46  SVR:new_iq DB:new_iq PORT:58116
              /database/new_iq/sybase/IQ-16_0/bin64/iqsrv16 @/database/new_iq/sybdb/new_iq.cfg /database/new_iq/sybdb/new_iq.db -gn 65 -o /database/new_iq/sybase/IQ-16_0/logfiles
${SYBASE}/IQ-16_0/bin64/start_iq -n utility_db -gu dba -c 48m -gc 20 -iqgovern 30 \
        -gd all -gl all -gm 10 -gp 4096 -ti 4400 -z -zr all -zo $SYBASE/IQ-16_0/logfiles/utility_db.out \
        -o $SYBASE/IQ-16_0/logfiles/utility_db.srvlog -iqmc 100 -iqtc 100 -x "tcpip{port=9000}"
Starting server utility_db on localhost at port 9000 (04/30 09:37:16)

Run Directory       : /database/new_iq/sybdb
Server Executable   : /database/new_iq/sybase/IQ-16_0/bin64/iqsrv16
Server Output Log   : /database/new_iq/IQLOG/utility_db.srvlog
Server Version      : 16.0.0.653/sp03 16.0.0/Linux 2.6.18-194.el5
Open Client Version : N/A
User Parameters     : '-n' 'utility_db' '-gu' 'dba' '-c' '48m' '-gc' '20' '-iqgovern' '30' '-gd' 'all' '-gl' 'all' '-gm' '10' '-gp' '4096' '-ti' '4400' '-z' '-zr' 'all' '-zo' '/database/new_iq/IQLOG/utility_db.out' '-o' '/database/new_iq/IQLOG/utility_db.srvlog' '-iqmc' '100' '-iqtc' '100' '-x' 'tcpip{port=9000}'
Default Parameters  : -gn 25
….

Remove the db, log and mir files:

rm instance.db instance.log instance.mir

Restore the full backup:

dbisql -c "uid=dba;pwd=sql;eng=utility_db;dbn=utility_db" -port 9000 -host $( hostname ) -nogui "restore.sql"

Restore the incremental backup(s):

dbisql -c "uid=dba;pwd=sql;eng=utility_db;dbn=utility_db" -port 9000 -host $( hostname ) -nogui "restore_incrementals.sql"

Stop the utility database:

stop_iq

Start the IQ server to ensure it comes up then shut it back down.

If the name of the server has changed (e.g. old_iq -> new_iq), then we need to update the log and mir files. First let’s find out where the log and mir files are currently set to in the db file:

dblog new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" is using log file "/database/old_iq/sybdb/old_iq.log"
"new_iq.db" is using log mirror file "/database/old_iq/sybdb/old_iq.mir"
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397583

Set the log file to “new_iq.log”:

dblog -t new_iq.log new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" was using log file "/database/old_iq/sybdb/old_iq.log"
"new_iq.db" is using log mirror file "/database/old_iq/sybdb/old_iq.mir"
"new_iq.db" is now using log file "new_iq.log"
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397625

We need to clear the mir file(s) before we can assign a new one:

dblog -r new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" is using log file "new_iq.log"
"new_iq.db" was using log mirror file "/database/old_iq/sybdb/db00.mir"
"new_iq.db" is now using no log mirror file
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397625

Set the mir file:

dblog -m new_iq.mir new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" is using log file "new_iq.log"
"new_iq.db" was using no log mirror file
"new_iq.db" is now using log mirror file "new_iq.mir"
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397625

Start your IQ instance.

Share Button

HOWTO: Truncate or Backup the Transaction Log in SAP Sybase IQ Data Warehouse

SAP Sybase IQ for more than a decade has resided with an embedded SQL Anywhere instance. SQL Anywhere handles the grunt tasks of managing connections and the like while IQ does what it does best: be a DATA WAREHOUSE. No, your 4 terabyte database doesn’t make it a data warehouse simply because it is big. A data warehouse is a complete different animal to your DBMS.

If you want to backup the transaction log, you simply need to issue a command:

dbbackup -x -c "uid=dba;eng=myiq;dbn=myiq;pwd=SQL" \
 /backups/iqtranlog/myiq.sybtran.dmp

If you need a quick truncation of the transaction log, you omit the file name:

dbbackup -x -c "uid=dba;eng=myiq;dbn=myiq;pwd=SQL"

Please note that you need to use double quotes not single quotes.

Sybase

Sybase

Share Button

RealNetworks releases RealDVD – waste of money?

On Twitter, Jim Louderback @louderb pointed out that RealNetworks released a controversial new product called RealDVD.  What is RealDVD?  It will copy your dvd movie, bit for bit, on to your harddrive with the CSS encryption intact.  For your enjoyment, it slaps on a DRM scheme as the dvd is being transferred to your computer.  That’s 4GB to 9GB of space taken up by a single movie.

Does this mean you can take the dvd copy you made and put it on your iPod or video player?  Nope.  The iPod and most portable mp4 players have no way to interpret the dvd copy.  So, what can you do with the dvd copy made by RealDVD?  Well, you can play it on your computer and possibly burn it to a dvd blank.  You can even play it on up to 4 other computers.  Forget about copying to other people even if you are able to strip the DRM, the dvd copy is watermarked with your information.

There are several FREE programs that will allow you to copy a dvd movie, bit for bit, without the DRM being added.  There are others, such as Handbreak, that will even rip the dvd and present you with a file ready to be uploaded to your iPod.

At this point, RealDVD doesn’t appear to be of any real value for us.  It does bring a lot of attention on to RealNetworks though.  Makes me wonder if this is nothing more than a marketing ploy.

The only way the MPAA will sanction this will be as direct competition to the iTunes movie Store.

Share Button