SAP IQ - Retrieving Transaction Log file path for IQ Database

[mail2s_shiva14]

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'...

[tayeb_hadjou · Accepted solution]

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>

[mail2s_shiva14 · Accepted solution]

Very thanks! It gave me the needed result. Next is I have to use it in my query.

-- With Thanks

[tayeb_hadjou]

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

[raymond_lackey]

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

[mail2s_shiva14]

SYSFILE; SYSDBFILE; SP_IQFILE - All these 3 System Objects didn't reveal the Transaction Log file path. All I see is IQ_SYSTEM_LOG in the file_name column instead of actual path of the file.

-- In 'thoughts'...