Results 1 to 9 of 9
  1. #1
    Join Date
    May 2003
    Posts
    4

    Thumbs up Unanswered: Hello all db2 experts!

    Dear all DB2 experts,

    How are you everybody? I have 2 COBOL programs running under Production environment and Test environment. I have 2 duplicate sets of tables running the same physical database in production and test environment. I now plan to make ONE COBOL program which can be usable under both Production and environment. Can any DB2 expert tell me how can I do this?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not exactly sure what you are trying to do, but I will offer one suggestion.

    I am assuming that your test and prod environments are in the same DB2, and the table names are the same, but the only difference is that the high level qualifier of the tables are different to distinguish the 2 environments.

    For example:
    TEST.EMPLOYEE
    PROD.EMPLOYEE

    If you want the one executable (linked) program with one bound plan/package to be able to update either of these two tables in the same execution, and the determination has to be made dynamically at execution time as to which you update, then you can use the following statement:

    SET CURRENT SQLID

    to set the implicit qualifier of all table, view, and alias names specified in dynamic SQL statements. Note that the SQL statements must be dynamic and not static SQL. The SQLID can be set to a host-variable, a literal, or the current USERID in effect while executing the program.

    Not sure if this is what you want, but hope it helps.

  3. #3
    Join Date
    May 2003
    Posts
    4

    Smile Your reply

    Dear Marcus,

    Thanks a lot. Your information helps me much. Thank you.

    Originally posted by Marcus_A
    I am not exactly sure what you are trying to do, but I will offer one suggestion.

    I am assuming that your test and prod environments are in the same DB2, and the table names are the same, but the only difference is that the high level qualifier of the tables are different to distinguish the 2 environments.

    For example:
    TEST.EMPLOYEE
    PROD.EMPLOYEE

    If you want the one executable (linked) program with one bound plan/package to be able to update either of these two tables in the same execution, and the determination has to be made dynamically at execution time as to which you update, then you can use the following statement:

    SET CURRENT SQLID

    to set the implicit qualifier of all table, view, and alias names specified in dynamic SQL statements. Note that the SQL statements must be dynamic and not static SQL. The SQLID can be set to a host-variable, a literal, or the current USERID in effect while executing the program.

    Not sure if this is what you want, but hope it helps.

  4. #4
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    If you have "2 duplicate sets of tables running the same physical database " - Are they using the same table space (ALIAS schema name)?

    Anyway, you don't even need to set the current sqlid - just reference the full schema name in the query...

    SELECT <data>
    FROM <prod>.EMPOYEE
    INNER JOIN <test>.EMPLOYEE
    ON <prod>.EMPLOYEE.SSN = <test>.EMPLOYEE.SSN
    WHERE <prod>.EMPOLYEE.SSN = '000000000';

    Honestly - your test and production environments should be on two seperate subsystems - (at least in my mind).....

    Take care,
    Rick

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Honestly - your test and production environments should be on two seperate subsystems - (at least in my mind).....



    Every techie will accept ...

    Convincing the non-technical decision maker is all that matters ... :-)

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Most would agree that production should be on another subsystem, but many shops have multiple test systems and it may not be feasible to have one sub-system for each, so the problem may be encountered anyway.

    For example, some shops have unit test, system test, integration test, acceptance test, etc.

    I don't know what prompted the original question, but I assume the program in question is not a normal program if it needs to access both environments at the same time, or in the same plan/package, dynamically determined at run time.

  7. #7
    Join Date
    May 2003
    Posts
    4

    Smile Thanks

    Thanks a lot first. Your information is useful too. I would like to clarify that initially one COBOL program is used in Test environment and one COBOL program is used in Production environment. "2 duplicate sets of tables running the same physical database " means that the table names under Test environment is Different from those of the Production environment but they contain the same contents and those tables are stored in same physical database. Now I want to make one COBOL program which be used in either PROD environment or TEST environment. Thanks. You take care too.

    Originally posted by Rick-dba
    If you have "2 duplicate sets of tables running the same physical database " - Are they using the same table space (ALIAS schema name)?

    Anyway, you don't even need to set the current sqlid - just reference the full schema name in the query...

    SELECT <data>
    FROM <prod>.EMPOYEE
    INNER JOIN <test>.EMPLOYEE
    ON <prod>.EMPLOYEE.SSN = <test>.EMPLOYEE.SSN
    WHERE <prod>.EMPOLYEE.SSN = '000000000';

    Honestly - your test and production environments should be on two seperate subsystems - (at least in my mind).....

    Take care,
    Rick

  8. #8
    Join Date
    Apr 2003
    Location
    Florida
    Posts
    79
    If I understand correctly - you have test/prod tables in the same production database.

    The easiest way I can think of to dynamically run either test or prod queries without rebind would be to code both sets of test/prod queries in the program and set a flag stating which environment to execute.
    Example.

    LINKAGE SECTION.
    ENVIRONMENT-FLAG PIC X.
    ....
    PROCEDURE DIVISION USING ENVIRONMENT-FLAG.
    etc.....
    IF ENVIRONMENT-FLAG = 'P'
    <execute production query>
    ELSE IF ENVIRONMENT-FLAG = 'T'
    <execute test query>
    ELSE
    <INVALID ENVIRONMENT FLAG>.

    Might work - never tried it myself.
    Rick

  9. #9
    Join Date
    May 2003
    Posts
    4

    Smile Reply

    Dear Mr Rick,

    Thank you so much. Your information is very useful to me. Thanks.


    Originally posted by Rick-dba
    If I understand correctly - you have test/prod tables in the same production database.

    The easiest way I can think of to dynamically run either test or prod queries without rebind would be to code both sets of test/prod queries in the program and set a flag stating which environment to execute.
    Example.

    LINKAGE SECTION.
    ENVIRONMENT-FLAG PIC X.
    ....
    PROCEDURE DIVISION USING ENVIRONMENT-FLAG.
    etc.....
    IF ENVIRONMENT-FLAG = 'P'
    <execute production query>
    ELSE IF ENVIRONMENT-FLAG = 'T'
    <execute test query>
    ELSE
    <INVALID ENVIRONMENT FLAG>.

    Might work - never tried it myself.
    Rick

Posting Permissions

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