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

Sybase ASE / MS SQL Server: Preventing a transaction from filling up the transaction log

Say you have a query that easily fills up the transaction log of the Sybase ASE server / Microsoft SQL Server:

delete from rep_queues_archive
where sample_date < dateadd(wk, -1, getdate())

How would you break up the transaction so it doesn’t fill up the log? There are several ways to do it, but I’ll just cover two of them:

  1. Break up the transaction based on the actual data.
    delete from rep_queues_archive 
    where sample_date < dateadd(wk, -52, getdate()
    delete from rep_queues_archive 
    where sample_date < dateadd(wk, -51, getdate())
  2. Break up the transaction based on the number of rows affected.
  3. declare @rows_affected int
    declare @dbName varchar(50)
    select @dbName = db_name()
    set rowcount 1000
    
    select @rows_affected = 1
    
    while @rows_affected > 0
      begin
         delete from rep_queues_archive where sample_date < dateadd(wk, -1, getdate())
         select @rows_affected = @@rowcount
     
         if @@error = 0
          begin
            commit tran
            dump tran @dbName with truncate only
          end
         else
          rollback tran
         end
    
    set rowcount 0

    Of course, if you are performing incremental backups, you will want to modify the dump tran @dbName with truncate only line to read something like dump tran @dbName to “/sybdumps/mydb_tran.dmp”. Modify to suit your naming standard.

Share Button