Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    8

    Question Unanswered: Questions about unix scripting

    Guys,

    I am preparing a script and one of the steps inside is doing db2 connect. Could anyone tell me why the following would work,

    export DB2DBDFT=$sdb
    db2 connect

    but the following would not,

    db2 connect to $sdb

    following the db2 connect there is a for-loop which builds db2 commands from the result of a query.

    Any idea?

    Andrew....

  2. #2
    Join Date
    Aug 2002
    Location
    UK
    Posts
    87
    An alias name for the database against which SQL statements are to
    be applied. The default is the value of the DB2DBDFT environment
    variable.
    the command db2 starts the db2 shell
    once the shell has started you can use the command
    'connect to <DBname>'


    (At lease this is how it works from the commadn line )


    Could you post an example of the FOR LOOP ?

  3. #3
    Join Date
    Dec 2002
    Posts
    8

    Cool Unix scripting question with example file attached

    Thanks you advice, Wingman

    Below attaches my unix script. Pls find time to have a look and reply.

    Andrew....
    Attached Files Attached Files

  4. #4
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Unix scripting question with example file attached

    Originally posted by andrewleung01
    Thanks you advice, Wingman

    Below attaches my unix script. Pls find time to have a look and reply.

    Andrew....
    Hey Andrew,

    in your unix script when you wrote "db2 connect user" is user the exact word in your script or is it the name of the user you want to connect to the database?

    Anyway. When you want to use the variable you exported, which is the name of the database you want to connect to, you should only use "db2 connect", which will perform a connection to your default database.
    Actually, "db2 connect" verifies if there is any active connections. If not, it stablishes a connection to your default DB. If you havenīt specified any Db as default, it will return a message that "A database connection does not exists"

    On the other hand, if you want to connect to a database using a certain userId, youīd have to use the whole "db2 connect to " statement specifying the name of the database and user ID, like this:
    "db2 connect to <DB> user <name_user> using <password>"

    in this case you have to provide the userīs password to authentify to the database. But I suggest you use the current ID, since you wouldnīt have to write the password in the script, the syntax for this is just: "db2 connect to <DB>".
    So, if it is possible, use the userID you want to connect to the database, to call the unix script and see if that works.

    HTH
    Fernando
    Last edited by F.OHANA; 12-13-02 at 09:29.

  5. #5
    Join Date
    Dec 2002
    Posts
    8

    Correction

    Fernando,

    Sorry, I mistyped the keyword 'user' after the db2 connect. It should be there.

    I could tell you what happened exactly. When I specified "db2 connect" and the global variable DB2DBDFT equal to the database I want to connect, it worked ok. However, when I exclude the variable and specified "db2 connect to DB user userid using password", the result of the SQL seemed having no effect on the for-loop.

    Also, it seemed I could only define the variable DB2DBDFT once in my script. When I changed the value of DB2DBDFT from one to another and issued db2 connect, the connection still pointed to the database of the previous DB2DBDFT value.

    Thanks for your help. Hope you could spot some light for me.

    Regards,
    Andrew
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: Correction

    Originally posted by andrewleung01
    Fernando,

    Sorry, I mistyped the keyword 'user' after the db2 connect. It should be there.

    I could tell you what happened exactly. When I specified "db2 connect" and the global variable DB2DBDFT equal to the database I want to connect, it worked ok. However, when I exclude the variable and specified "db2 connect to DB user userid using password", the result of the SQL seemed having no effect on the for-loop.

    Also, it seemed I could only define the variable DB2DBDFT once in my script. When I changed the value of DB2DBDFT from one to another and issued db2 connect, the connection still pointed to the database of the previous DB2DBDFT value.

    Thanks for your help. Hope you could spot some light for me.

    Regards,
    Andrew
    Heys,

    Another option would be using the explicit connect statement specifying the name of the database. And when you finish using the DB, after the "Connect Reset", issue a "db2 disconnect <DB>", and after that try a new connection to another database using "db2 connect to <DB>".
    And imo, you wouldnīt need to define the variable DB2DBDFT.

    Although, if you didnīt understand what I tried to tell you, I could try to change your script and mail it to you.

    HTH
    Fernando

  7. #7
    Join Date
    Dec 2002
    Posts
    8

    something found

    Fernando,

    My script now could manage to connect to one database to export and to then the other to load data. Before lanuching my previous reply I had already tested and awared there would be a problem when I said "db2 connect to $db user $id using $pw" together used "for" loop to generate statements from the result set. It would only worked when I defined the db2 profile registry variable DB2DBDFT=$db and issued "db2 connect" afterwards. In fact I realised my script actually established connections to both source and target dbs for export and load. Before I was trapped by the output of the database connection statements which showed two connections were pointing to one database. Actually this was not true since there were some tables in the source db not existing in the target db. I realised when I came back and checked the content of the export and load statements.

    Now one barrier is down, but there is still one more to break. At the moment I have to use the same userid to establish connections to both source and target dbs. As the matter of the fact I prefer to be able to use two separated userids. This is safer since one userid would only have write permission to one db but not the others.

    Any idea?

    I also attach my modified script thus you could have a try.

    Thanks,
    Andrew
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2002
    Location
    Rio de Janeiro - Brazil
    Posts
    78

    Re: something found

    Originally posted by andrewleung01
    Fernando,

    My script now could manage to connect to one database to export and to then the other to load data. Before lanuching my previous reply I had already tested and awared there would be a problem when I said "db2 connect to $db user $id using $pw" together used "for" loop to generate statements from the result set. It would only worked when I defined the db2 profile registry variable DB2DBDFT=$db and issued "db2 connect" afterwards. In fact I realised my script actually established connections to both source and target dbs for export and load. Before I was trapped by the output of the database connection statements which showed two connections were pointing to one database. Actually this was not true since there were some tables in the source db not existing in the target db. I realised when I came back and checked the content of the export and load statements.

    Now one barrier is down, but there is still one more to break. At the moment I have to use the same userid to establish connections to both source and target dbs. As the matter of the fact I prefer to be able to use two separated userids. This is safer since one userid would only have write permission to one db but not the others.

    Any idea?

    I also attach my modified script thus you could have a try.

    Thanks,
    Andrew
    Hey Andrew,

    Nice to hear that, although, as far as I can see, that is a much more difficult task. I canīt see anyother way of using two UserIds without writting the password of one of them either on the script itself, or at a file read by the script. But anyway, thatīs not a really safe solution.

    Note: When I said the "Connect to" statement, you could use just: "Connect to <DB>" without the "user" and "using" parameters. (just as a point)

    Well, another way that could be used is to have different privileges and authorities for that user on the databases, I mean: On DB1 heīs only able to read and on DB2 heīs only able to write.

    By the way, are those two databases on the same instance? they are, right?

    The above suggestion is the only way I can see to connect to those 2 DBs with users that have different privileges and authorities without having to write the password down is that: using the same userID with different privileges on each Database.

    Thatīs as far I could go.
    HTH
    Fernando

Posting Permissions

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