Skip to Content
0
Jul 18, 2020 at 05:53 AM

SAP IQ - Retrieving Transaction Log file path for IQ Database

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