Sybase IQ schedule indexing

[former_SQLA_member1695001]

I have a Sybase IQ with below details: SAP IQ/16.0.110.2014/10214/P/sp11/MS/Windows 2003/64bit/2015-11-23 12:53:55

I need to schedule the indexing. Can do it manually by running the output of below query, but can't figure out the way to schedule it :

select 'sp_iqrebuildindex ''DBA.' + T.[table_name] + ''', ''index ' + I1.index_name + ' retier ''' + char(13) + ' go' from sysidx I1, sysiqidx I2, systab T where T.table_id = I1.table_id and (I2.table_id=I1.table_id and I2.index_id = I1.index_id) and T.creator = 1 and I2.index_type = 'HG' Union All select 'sp_iqrebuildindex ''DBA.' + T.[table_name] + ''', ''index ' + I1.index_name + '''' + char(13) + ' go' from sysidx I1, sysiqidx I2, systab T where T.table_id = I1.table_id and (I2.table_id=I1.table_id and I2.index_id = I1.index_id) and T.creator = 1 and I2.index_type <> 'HG'

[chris_keating · Accepted solution]

Have you considered

• an OS based scheduled task tool such as Windows Scheduler or unix 'at' or 'crontab'?
• a database event

[former_SQLA_member1695001 · Accepted solution]

Yes i have considered, but bottleneck is can't execute the output of below query:

select 'sp_iqrebuildindex ''DBA.' + T.[table_name] + ''', ''index ' + I1.index_name + ' retier ''' + char(13) + ' go' from sysidx I1, sysiqidx I2, systab T where T.table_id = I1.table_id and (I2.table_id=I1.table_id and I2.index_id = I1.index_id) and T.creator = 1 and I2.index_type = 'HG' Union All select 'sp_iqrebuildindex ''DBA.' + T.[table_name] + ''', ''index ' + I1.index_name + '''' + char(13) + ' go' from sysidx I1, sysiqidx I2, systab T where T.table_id = I1.table_id and (I2.table_id=I1.table_id and I2.index_id = I1.index_id) and T.creator = 1 and I2.index_type <> 'HG'

if i try a simple below query i get error "Result set not permitted in '<batch statement="">'":

declare @str char(50) set @str = 'select 1' execute (@str)

[chris_keating · Accepted solution]

The @sql should either be an individual column row (representing a single sp_iqirebuildindex stmt) or a concatenation of those statements. This could be done in a cursor. See FETCH NEXT [SP] example code for example of cursor processing. Here is a rough idea of how it might be achieved.

DECLARE c_rb_idx CURSOR FOR <select stmt=""> LOOP FETCH NEXT c_rb_idx INTO @stmt ... -- EXEC( @stmt) -- SELECT @stmt = @stmt || @sql ... END LOOP

[former_SQLA_member1695001 · Accepted solution]

Thanks for your suggestions, instead of cursor i opted for temporary table below is my query, appreciate your support:

CREATE TABLE #TempSQLStatements ( SqlStatement VARCHAR(9999) );

-- Insert the SQL statements into the temporary table INSERT INTO #TempSQLStatements (SqlStatement) select 'sp_iqrebuildindex ''DBA.' + T.[table_name] + ''', ''index ' + I1.index_name + ' retier ''' from sysidx I1, sysiqidx I2, systab T where T.table_id = I1.table_id and (I2.table_id=I1.table_id and I2.index_id = I1.index_id) and T.creator = 1 and I2.index_type = 'HG' and T.[table_name] = 'TargetTable' Union All select 'sp_iqrebuildindex ''DBA.' + T.[table_name] + ''', ''index ' + I1.index_name + '''' from sysidx I1, sysiqidx I2, systab T where T.table_id = I1.table_id and (I2.table_id=I1.table_id and I2.index_id = I1.index_id) and T.creator = 1 and I2.index_type <> 'HG' and T.[table_name] = 'TargetTable'

-- Declare variables DECLARE @sql VARCHAR(999)

-- Loop through the temporary table and execute SQL statements WHILE EXISTS (SELECT 1 FROM #TempSQLStatements) BEGIN SELECT TOP 1 @sql = SqlStatement FROM #TempSQLStatements

-- Execute the SQL statement
Execute (@sql)

-- Remove the executed statement from the temporary table
DELETE FROM #TempSQLStatements WHERE SqlStatement = @sql

END;

-- Drop the temporary table DROP TABLE #TempSQLStatements