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'

asked 08 Sep '23, 03:32

sunilbablani's gravatar image

sunilbablani
3013
accept rate: 0%


Have you considered

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

answered 08 Sep '23, 07:26

Chris%20Keating's gravatar image

Chris Keating
7.8k49128
accept rate: 32%

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)

(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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×41
×32

question asked: 08 Sep '23, 03:32

question was seen: 271 times

last updated: 08 Sep '23, 10:27