|
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' |
|
Have you considered
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="">'":
(08 Sep '23, 07:38)
sunilbablani
1
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
(08 Sep '23, 09:40)
Chris Keating
Replies hidden
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
(08 Sep '23, 10:27)
sunilbablani
|
