Skip to Content
author's profile photo LonelyRogue LR
0
LonelyRogue LR

SAP IQ - Retrieving Transaction Log file path for IQ Database

Posted on Jul 18, 2020 at 05:53 AM 121 Views

Hello -

How do I retrieve the IQ Transaction Log path for IQ Database? I tried

SELECT * FROM SYS.SYSDBFILE;
SELECT * FROM SYS.SYSIQFILE;
SELECT * FROM SYS.SYSDBSPACE

but none of these System Tables gave the full path and instead shows IQ_SYSTEM_LOG while expect the actual file path as given when CREATE DATABASE was executed '/opt2/sybaseiqlogs/translogs/IqDB.log'

"DBFile_Id","DBFile_Name","FilePath"
32702,IQ_SYSTEM_LOG ,"IQ_SYSTEM_LOG"

I intend to create a RESTORE DATABASE script like in below -

SELECT -1 DBFile_Id,'' DBSpace_Name, '' FilePath,'RESTORE DATABASE ''New_IQ'' FROM ''BackupPath/.Bak ''' RestoreCmd
UNION ALL
SELECT DBFile_Id, DBFile_Name, File_Name FilePath,'RENAME '||DBFile_Name||' TO ''/TempPath/'|| File_Name ||''''   FROM SysDBFile ORDER BY  DBFile_Id;

While I could see physical file paths for every DB file but not for Trans Log. Please provide help.

-- In 'thoughts'...

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Tayeb HADJOU
    Tayeb HADJOU
    Posted on Jul 20, 2020 at 10:28 AM
    2

    To retrieve transaction log file name/location and its mirror (if defined) :

    1-

    select db_property('LogName'), db_property('LogMirrorName')

    or

    2-

    dblog <your_dbfile.db>

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Raymond Lackey
    Raymond Lackey
    Posted on Jul 18, 2020 at 01:25 PM
    0

    Try:

    SELECT dbf.dbfile_name, f.* FROM SYSFILE f, SYSDBFILE dbf WHERE f.file_id=dbf.dbfile_id

    or

    sp_iqfile [ dbspace-name ]

    For your restore I suggest building a script similar to this:

    -- Get dbspace and IQ file names and add -- rename syntax including quotation marks select 'rename' as 'restore ... rename' , dbf.dbfile_name as 'IQ file' , 'to' as 'to' , '''' + f.file_name + '''' as 'file_path' from SYSFILE f, SYSDBFILE dbf where f.store_type=2 and f.file_id=dbf.dbfile_id -- Send output to a file in proper format -- without delimiters or extra quotation marks output to restore.tst delimited by '' quote ''; -- This produces a restore.tst file like the following: -- rename IQ_SYSTEM_MAIN to '/dev/rdsk/c2t0d1s7' -- rename IQ_SYSTEM_TEMP to '/dev/rdsk/c2t1d1s7' -- rename IQ_SYSTEM_MSG to 'all_types.iqmsg

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Tayeb HADJOU
    Tayeb HADJOU
    Posted on Jul 21, 2020 at 01:34 PM
    0

    You intend use "rename" to restore IQ database to a different dbfiles location.

    Do NOT include system files (.db, .log and temporary) in rename clause.

    They are managed in different way.

    .db is specified in "restore '<path>/file.db> from ..."

    Transaction log file is specified once restore finsied, using "dblog -t ..." command

    System Temporary files is place by default in /tmp or by environement variables :

    Manage Locations of Temporary Files

    See also

    KBA

    2467860 - How to write RENAME clause for RESTORE to a new location - SAP IQ

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.