RE: SQL index authority

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

How about creating a stored procedure that takes library and index name as
input and call it instead.  Your SP can then build the command string and
call QCMDEXC dynamically.  Something like:

create procedure grantIndexAuthority(library varchar(10), index varchar(10))
language sql
begin 
  declare sqlStmt varchar(70);
  declare len decimal(15,5);
  set sqlStmt = 'GRTOBJAUT OBJ(' || library || '/' || index || 
                ') OBJTYPE(*FILE) USER(*PUBLIC) AUT(*CHANGE)';
  set len = length(sqlStmt);
  call qcmdexc(sqlStmt,len);
end;

You'd then call it like:

Call grandIndexAuthority('LIBRARY','INDEXNAME');

Elvis

Mike Cain - DB2 for i5/OS Temporary Indexes - The Good, The Bad, The Ugly
October 16 
2007 System i Fall Technical Conference | Orlando | November 4-7
Celebrating 10-Years of SQL Performance Excellence on IBM System i, eServer
iSeries and the server affectionately known as the AS/400 

-----Original Message-----
Subject: RE: SQL index authority

Thanks, Elvis.

Using QCMDEXC as a stored procedure would work except that I do not want 
to hardcode the library name in the GRTOBJAUT cmd.  Also, I cannot use the 
libl that is active when the RUNSQLSTM cmd is executed.  Looks like I'll 
just have to put some comments in the source telling the next person to be 
sure and execute the GRTOBJAUT after running the RUNSQLSTM cmd.  Thanks 
for the idea, though.