We often grant permissions against table for users. Some use the GUI, some just script it out. I wrote a quick stored procedure that will take couple of input parameter and run the grant against the tables. Just for the fun of it, I wrote it for both DB2 UDB and SQL Server. I know there are more than one way to write, but here is one way.
CREATE PROCEDURE Grant_TablePermissions (
IN oschema varchar(20),
IN permissions varchar(30),
IN grantee varchar(30),
OUT counter smallint
)
DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE vstmt varchar(3000);
DECLARE vname varchar(30);
DECLARE vcounter smallint default 0;
DECLARE at_end smallint default 0;
DECLARE cursor1 CURSOR WITH RETURN for
SELECT NAME FROM SYSIBM.SYSTABLES
WHERE CREATOR = oschema ORDER BY NAME;
DECLARE EXIT HANDLER FOR NOT FOUND
SET at_end = 1;
SET at_end = 0;
OPEN cursor1;
fetch_loop:
REPEAT
FETCH cursor1 INTO vname;
SET vstmt = ‘grant ‘||permissions||’ on ‘||oschema||’.'||vname||’ to ‘||grantee;
PREPARE s1 FROM vstmt;
EXECUTE s1;
SET vcounter = vcounter + 1;
SET counter = vcounter;
UNTIL at_end = 1
END REPEAT fetch_loop;
CLOSE cursor1;
END P1
CREATE procedure [dbo].[Grant_TablePermission]
@permissions varchar(30),
@grantee varchar(30)
as
begin
declare @sqlcmd nvarchar(4000);
declare @tablename nvarchar(50);
declare @RowCount int;
select @RowCount = 0;
declare tablecursor insensitive cursor for
select name from sys.tables
open tablecursor;
fetch tablecursor into @tablename;
while (@@FETCH_STATUS = 0)
begin
select @sqlcmd = ‘GRANT ‘ + @permissions + ‘ on dbo.’+ @tablename + ‘ TO ‘+ @grantee +’;';
execute sp_executeSQL @sqlcmd;
print @sqlcmd;
set @RowCount = @RowCount + 1;
fetch tablecursor into @tablename;
end;
close tablecursor;
deallocate tablecursor;
SELECT ‘Number of tables permissions has been granted: ‘ + CONVERT(varchar, @RowCount)
END
Data movement is a common task that app dev and dbas perform often. DB2 has multiple options to mass add data to tables : IMPORT, LOAD and DB2MOVE. Each of them have its own pros and cons. If you are loading lots of data and you need it done fast, but can compromise on availability then LOAD is your best bet. The load command in DB2 LUW is loaded with different options. I am sure you have loaded ixf and del files to db2 tables. What if you want to make a copy of a table on the same database ? The traditional method would be to create a new table like the source table, then export the data in del or ixf format and then load the data using the LOAD command. A newer approach would be to use the cursor option in the load command . Create table, declare a cursor for the source table and load the target table using the cursor. Eg :
declare c1 cursor select * from ds1.sales;
load from c1 of cursor messages load_sales.msg insert into ds1.sales_stage;
So what if you have to load data from another database (same server or different server) ? First you need to catalog the remote database on the server and then you can declare a cursor for a remote database and then load using that cursor. Eg:
Assuming you have cataloged the source database on the target server as srcdb
connect to targetdb;
declare c1 cursor database srcdb user user1 using secretpass for select * from ps1.sales;
load from c1 of cursor messages load_sales.msg insert into ds1.sales_stage nonrecoverable;
This will load the data on table ps1.sales from the srcdb to ds1.sales_stage on the targetdb eliminating a file extract and then a load from the file.
