Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: server creation dynamically

    Hi,

    How to create a server dynamically on db2.
    this is my code.

    'CREATE SERVER "MENTIS_SERVER" TYPE DB2/UDB VERSION ''10.5''
    WRAPPER MENTIS_WRAPPER AUTHORIZATION XXXXXX PASSWORD "********"
    OPTIONS ( ADD DBNAME ''DB_NAME'' )';

    please let me know the outlines of the procedure for this.
    thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so : what seems to be the problem. I only see the command for defining a federated server
    If the syntax is not clear have a look at infocenter and look for federation / federated server
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jul 2014
    Posts
    294

    server creation dynamically

    CREATE OR REPLACE PROCEDURE INNER_PROC(IN DB_NAME VARCHAR(100))
    LANGUAGE SQL
    BEGIN
    DECLARE SQLCODE INTEGER;
    DECLARE STMT2 VARCHAR(100);
    DECLARE DB_NAME1 VARCHAR(100);
    DECLARE CURSOR1 CURSOR WITH RETURN FOR
    SELECT DATABASE_NAME FROM VENKATASD.DBLIST;
    OPEN CURSOR1;

    FETCH FROM CURSOR1 INTO DB_NAME1;
    WHILE ( SQLCODE = 0 )
    DO

    EXECUTE IMMEDIATE 'DROP SERVER MENTIS_SERVER';

    SET STMT2= 'CREATE SERVER "MENTIS_SERVER" TYPE DB2/UDB VERSION ''10.5''
    WRAPPER MENTIS_WRAPPER AUTHORIZATION VENKATASD PASSWORD "WORDLEND"
    OPTIONS (ADD DBNAME "DB_NAME1" )';

    EXECUTE IMMEDIATE STMT2;
    FETCH FROM CURSOR1 INTO DB_NAME1;
    END WHILE;
    --CLOSE CURSOR1---;
    END

    error:

    {call VENKATASD.INNER_PROC(?)}
    "DBNAME" is a required "SERVER" option for "MENTIS_SERVER".. SQLCODE=-1883, SQLSTATE=428EG, DRIVER=3.67.28
    Run of routine failed.
    - Roll back completed successfully.


    Please help me I am very new to all these things as I am learning now.
    Forgive me if there are blunders

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    PLEASE read the doc about this command/facility : federation server
    why creating an sp the execute the command once
    why not just executing in command line and see
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Jul 2014
    Posts
    294

    server creation dynamically

    Thanks for the reply,
    I can execute this command from my command line.
    this is my command

    db2 => CREATE SERVER "MENTIS_SERVER" TYPE DB2/UDB VERSION '10.5'
    db2 (cont.) => WRAPPER MENTIS_WRAPPER AUTHORIZATION VENKATASD PASSWORD "worldend"
    db2 (cont.) => OPTIONS (ADD DBNAME 'sample' );

    Now from my procedure I am sending the DBNAME parameter at runtime , these DBNAMES are in a separate table.
    According to my knowledge There is table (DBLIST) with few database names.onmy procedure I am reading those DBNAME's from that DBLIST table & passing them on this CREATE SERVER command to create a remote server for each database. I am trying this through a while loop.
    Correct me IF i am wrong
    Last edited by HABBIE; 08-05-14 at 09:07. Reason: clear description

  6. #6
    Join Date
    Sep 2013
    Location
    India
    Posts
    246
    Hi Habbie,

    "OPTIONS (ADD DBNAME "DB_NAME1" )';"

    Error is generating in above line. It seems Db2 is Expecting DB_name valued within single quotes ('TEST_DB').

    It looks substituting DB_NAME1 with value from the table column is not adding single quotes to it resulting in error.

    You may try concatenating single quotes while preparing stmt2.

    Help from URL: EXECUTE IMMEDIATE - SQL PL Guide for DB2


    revert with the outcome.
    ssumit

  7. #7
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    Strange and dangerous code.
    - Why to drop and create a server with the same name in loop?
    - Do you always have only 1 row in the VENKATASD.DBLIST table?
    - What if a server with the "MENTIS_SERVER" name doesn't exist before a procedure call? You don't handle a possible error for DROP.
    A server creation might look like this:
    Code:
    BEGIN
      for v as 
        select 
          'CREATE SERVER "MENTIS_SERVER" TYPE DB2/UDB VERSION ''10.5'' '
        ||'WRAPPER MENTIS_WRAPPER AUTHORIZATION VENKATASD PASSWORD worldend '
        ||'OPTIONS (ADD DBNAME '''||DATABASE_NAME||''' )' as stmt
        from VENKATASD.DBLIST
      do
        execute immediate v.stmt;
      end for;
    END
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •