Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: DB2 Color Connect !! for Windows

    Hi All,

    DB2 V9.5.5 on Win2K3

    In our previous weekly meeting one of the prevoius DBA (currently not a DBA) told his story about mistakenly modifying the production tables instead of staging tables.

    We use command line a lot , so I extended my connect batch file to make it generic. I am sure everybody might be using some of such scripts to safegaurd against such mistakes.

    To avoid such situations I use command box text colors to reflect DB am I connected to. Also, I use RED on BLACK to indicate connection error. I keep PRODUCTION DB connections seperate colored from staging or development DBs.

    The script is connect.bat. There is no naming restrictions. In fact I use cn.bat as a general script,posdb.bat, mirrordb.bat etc for as specific scripts. This script can use connections definitions as connect.ini external file. Or you may add these definitons to the end of script itself.

    The syntax is cool. You may configure easily it to your taste. Minimum is no parameters, it will try script name ( without ext) as DB name. Then you may supply DB name, username and password. You cannot supply color definition from commad line ( that is the purpose - keep colors fixed ).

    Syntax and other info is included in script itself. For colors use COLOR /? in command box for help.

    Example
    C:\Program Files\IBM\SQLLIB\BIN>cn ecx

    CONNECT TO ecx user db2admin using

    Database Connection Information

    Database server = DB2/NT64 9.5.5
    SQL authorization ID = DB2ADMIN
    Local database alias = ECX

    C:\Program Files\IBM\SQLLIB\BIN>cn

    CONNECT TO cn

    Database Connection Information

    Database server = DB2/NT64 9.5.5
    SQL authorization ID = DB2ADMIN
    Local database alias = CN

    C:\Program Files\IBM\SQLLIB\BIN>
    Connect.bat
    Code:
    ::--------------connect.bat--------------Db2WindowsColorConnect.bat------::
    ::--Code Name : connect.bat
    ::--Desc Name : Db2WindowsColorConnect.bat
    ::--Prod date : 07-May-2010
    ::--Syntax    : connect db usr pwd
    ::-------------All parameters are optional and 
    ::-------------if no parameter is specified then script name is taken
    ::-------------to be DB name.
    ::--Uses      : .ini file is used (optionally) to get DB info user and
    ::-------------password. if not specified, it uses itself as ini file
    ::--Tips      : Too lazy to type, save each copy as %db%.bat at first
    ::-------------location in PATH string,also include ini info at the
    ::-------------end of file. An example is included. CFG var is used
    ::-------------as .ini file desc, if file is not found, it tries to
    ::-------------read itself to find specified or implied DB name.
    ::--INI format: db color user pwd
    ::-------------All are optional.Omitted color,default is used,
    ::-------------ommited username, tries to connect without username
    ::-------------pwd ommited, db2 will prompt for password.
    ::-------------Error Color as '-ERROR-'  if specified
    ::-------------Dfault Color as '-DEFAULT- if specified
    ::-------------Use no color with one DB : use 'NONE'  as color.
    ::--Modified  : 
    ::----------------------------------------------------------------------::
    
    @echo off
    SetLocal
    ::---------user configurable--::
    ::Set "DB=POSD"
    Set "DB=%1"
    ::Set "CFG=cn.ini"
    Set "CFG=%~n0.ini"
    ::----------------------------::
    
    ::-------------------prepend path to cfg filename--::
    if NOT "%CFG%"=="" Set "CFG=%~dp0%CFG%"
    ::-------------------------------------------------::
    if "%CFG%"=="" Set "CFG=%~dpnx0"
    if %DB%.==. Set "DB=%~n0"
    ::---------------------------above 2 or below 2 ---::
    if %DB%.==. goto :eof
    if not exist "%CFG%" goto :eof
    ::-------------------------------------------------::
    Set CFG="%CFG%"
    for /f "USEBACKQ TOKENS=1-4" %%w in (%CFG%) do if /i %%wx==%DB%x set "color=%%x" & Set "user=%%y" & Set "pwd=%%z"
    for /f "USEBACKQ TOKENS=1-4" %%w in (%CFG%) do if /i %%wx==-DEFAULT-x set "DCOLOR=%%x" 
    for /f "USEBACKQ TOKENS=1-4" %%w in (%CFG%) do if /i %%wx==-ERROR-x set "ECOLOR=%%x"
    ::-----------------------------check if username and pwd are specified---::
    if %2x neq x Set "user=%2"
    if %3x neq x Set "pwd=%3"
    if %user%x neq x (set "user=user %user%")  else (set "user=")
    if %pwd%x neq x (set "pwd=using %pwd%")  else (set "pwd=")
    ::-----------------------------Adjust if COLOR found/not found or NONE---::
    if "%COLOR%"=="" if "%DCOLOR%"=="" (Set "COLOR=NONE") else (Set "COLOR=%DCOLOR%")
    if /i %COLOR%x==NONEx (set "SetColor= type nul") else (set "SetColor=COLOR %COLOR%")
    if /i %ECOLOR%x==x (set "eColor= type nul") else (set "eColor=COLOR %ECOLOR%")
    db2 echo
    db2 -v CONNECT TO %DB% %user% %pwd%
    if not errorlevel 1 ( %SetColor%  ) else ( %ECOLOR%  ) 
    EndLocal
    EXIT /b
    ::----------END OF SCRIPT----------::
    
    ::-----------------------------following section is .ini entries -------------::
    ::--------Color and password definition--Db2WindowsColorConnect.ini------::
    
    ------------------------------Each Production db has unique color each
    POSDB F2
    GAMDB FD
    ------------------------------All Staging has F4  -RED on WHITE
    POSS NONE db2admin mypwd
    GAMS f4 db2admin password
    ------------------------------All Mirrors has F6  -YELLOW on WHITE
    POSM F6 db2admin secretpwd
    GAMM F6
    ------------------------------All Staging has F4  -PURPLE on WHITE
    POSP F5
    GAMP F5
    ------------ERROR and DEFAULT colors are defined in one line each----------
    
    ------------------------------ERROR 04 -- Light RED on BLACK
    -ERROR- 0C
    ------------------------------DEFAULT F0 -- BLACK on WHITE
    -DEFAULT- F0
    
    ::-------------eof--Db2WindowsColorConnect.ini------------------------------::
    Connect.ini
    Code:
    POS F2 db2admin password
    GAMING FD db2admin pwd
    HIGHPOS F9 db2admin secret
    HIGHGAM FC db2admin mypwd
    ------------------------------All Staging has F4  -RED on WHITE
    POSS F4 db2admin postag
    GAMS f4 db2admin gastag
    ------------------------------All Mirrors has F6  -YELLOW on WHITE
    POSM f6 db2admin pomirror
    TRIUMPHM F6 db2admin gamirror
    ------------------------------All DRP has F4  -PURPLE on WHITE
    POSP f5 db2admin passdrp
    GAMP f5 db2admin passdrp
    
    ------------------------------All develpment has F6  -PURPLE on WHITE
    POSD f6 db2admin devpass
    GAMD f6 db2admin devpass
    
    ------------------------------All portal has F3  -PURPLE on WHITE
    PORTALP f3 db2admin npsdevpos
    PORTALG f3 db2admin npsdevgam
    
    ------------------------------ERROR 04 -- Light RED on BLACK
    -ERROR- 0C
    ------------------------------DEFAULT F0 -- BLACK on WHITE
    -DEFAULT- F0
    Hope this will be useful.

    Need more info plz let me know.


    DBFinder
    Last edited by DBFinder; 05-09-10 at 11:06.

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
  •