Merry Christmas !

Posted in internet, Misc | Tagged , , , | Leave a comment

Stored Proc for SQL Server and DB2 UDB

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.

DB2 UDB:

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

SQL Server:

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

Posted in database, db2, sql server | Tagged , , , , , , , , , | Leave a comment

Sending an email in unix with attachments

Mailx is an easy way to send email from your unix/linux system.  With mailx you can get your server to send regular email or attach a file using your smtp server.  Ensure your sendmail is configured to let mailx send emails.  A good application for using mailx would be to send alerts, or process a file and then email it to somebody, extract data from your database or etl system and email the resulting data or file, etc.

Here is how to send an email :

mailx -s “Subject goes here” “toemail@address” “body goes here”

mailx -r “sender@emailaddress” -s ”Subject” “toemail@address” “body goes here”

If the body is on a file :

mailx -s “Subject goes here” “toemail@address” < body.txt

If you want to send an attachment in the file :

uuencode data.csv data.csv|mailx –s “Subject goes here” “toemail@address”

If you want to send an email with body and attachment :

uuencode data.csv data.csv > datafile.csv  
cat body.txt datafile.csv > combined.file
mailx -s “Subject goes here” “toemail@address” < combined.file

The above three lines of code will enable you to send an email with body and an attachment.

Posted in database, how to, linux, Misc, unix | Tagged , , , , , , , , , , , , , | Leave a comment

Loading db2 tables from another database

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.

Posted in db2, how to | Tagged , , , , , , , , , , | Leave a comment