0

I am trying to write a bash script that does some db2 work. I am facing the issue whereby subshells require db2 connections to be reconnected.

Unfortunately, reconnecting every time is dog slow, as around 1000 new connections will need to be made, each taking around a second. Thus I'm looking to avoid these subshell reconnections.

Googling around this situation, I have found that there is a way to set the default connection schema using the variable DB2DBDFT. Unfortunately, with this set it takes the linux logged in user name to be the DB schema name you are running as, whereas our system has a different DB user name to the user logged in to bash.

1) Is there a way to set the DB2DBDFT variable, but make it so that it can use a "user abc using 123" style syntax, or if there are other variables where you can set these (I have searched but not found them).

2) If there is, will this actually save me any time? Obviously if I find a solution I will benchmark, but I'm not sure that even if I find this, it will be quicker, and surely it will slow down all other subshell spawns.

3) I'm very open to other suggestions, I've tried piping to while statements rather than calling subshells but then remembered this has the same issues.

e.g.

    result=$(call_procedure "$get_sell_price_old_sql")

versus

    call_procedure "$get_sell_price_old_sql" | while read result_
    do
            result=$result_
    done

4) I'm sure there are other avenues I've not considered, any advice and suggestions greatly appreciated.

Many thanks, Mitch.

Mitch Kent
  • 574
  • 5
  • 23
  • variables like DB2DBDFT, DB2USER, DB2PASSWD? – Davide May 13 '13 at 13:25
  • Davide - in all the IBM documentation I read, I didn't see the DB2USER and DB2PASSWORD variables listed as options. When using google to search for the terms "db2set db2user" or "export db2user" there were no results I found either. If they do indeed exist then that would be great, and I will search again for references. – Mitch Kent May 13 '13 at 13:39
  • well, I'm pretty sure they exist, since i got finger-muscle trained to write the "db2 connect to $DB2DBDFT user $DB2USER using $DB2PASSWD" string (not password, passwd) in almost every shell, since i had your very same problem ;) – Davide May 13 '13 at 13:42
  • forget it, I forgot I had defined them myself in my .profile (export DB2USER=myuser) and so on... sorry :( – Davide May 13 '13 at 13:52
  • Yeah I guessed as much. `db2set -lr` will give you the variables i think... Thanks though! – Mitch Kent May 13 '13 at 13:53
  • yep, exactly, that's what my "sorry" was for, I got so used to them to take for granted that they were db2 vars :( Btw, to speed up things, did you try sourcing the call_procedure? That wouldn't spawn subprocesses, I think – Davide May 13 '13 at 13:59
  • call_procedure is a function within the script rather than external script, not sure that's an option, but that's the kind of thing I'm looking to try! I have a fully working script now so this is now relegated to the "nice-to-have's", will just help speed it up. Someone suggested DB2ACCOUNT but I've not found an example of what formatting it expects yet... – Mitch Kent May 13 '13 at 15:34
  • Using `db2set DB2ACCOUNT=user_name` didn't do the job. – Mitch Kent May 13 '13 at 15:46

1 Answers1

0

To answer your 2nd question, no, this is unlikely to make the process faster. DB2DBDFT only specifies the name of the database to connect to if a connection does not exist, so the client will still be connecting each time you run any SQL statement. And no, you cannot specify the user for an implicit connection, it will always be the logged-in OS user.

Typically the database connection should be pretty fast, unless it is the very first connection to an inactive database, in which case what takes most time is the database activation. If you are the only user connecting to the database when the script runs, try issuing the db2 activate db <your db> command before connecting, that will keep it active between connections.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • Thanks @mustaccio. My understanding was that it wouldn't be any faster for the reasons you state, and giving the "activate db" command first also doesn't speed up the process as I also benchmarked this. I don't think the db connection is necessarily slow, I just think that it is the re-connection 1000 times that is the hindrance. Unfortunately, I've not been able to remove this without a complete re-write of how the bash script runs. Thanks for your advice. – Mitch Kent May 20 '13 at 13:43