Random() function giving an error with LUW DB2

[manojredward]

Hi Team, We are comparing the data between LUW DB2 and BQ and our validation are in YAML files. While executing the ROW validation, we gave use_random_rows = True and random_row_batch_size = '1000' and it fails to execute the random rows selection.

Points for your reference

1. We are using Cloud Run Job to execute the DVT
2. Our input validation files are in YAML format and stored in GCS bucket
3. Our connection json is also in GCS and we are overwriting PSO_DV_CONN_HOME environment variable and use it to execute cloud run job

Error: 08/22/2024 05:13:45 PM-ERROR: Error (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2] SQL0440N No authorized routine named "RANDOM" of type "" having compatible arguments was found. SQLSTATE=42884 SQLCODE=-440

Input YAML file: result_handler: project_id: table_id: type: BigQuery source: db2 target: bq validations:

• case_insensitive_match: false comparison_fields:
  • cast: null field_alias: ADDR_TYPE_ID_cell_level_validation source_column: addr_type_id target_column: ADDR_TYPE_ID
  • cast: null field_alias: LANG_CD_cell_level_validation source_column: lang_cd target_column: LANG_CD
  • cast: null field_alias: ADDR_TYPE_TEXT_cell_level_validation source_column: addr_type_text target_column: ADDR_TYPE_TEXT filter_status: null filters: [] format: table labels: [] primary_keys:
  • cast: null field_alias: ADDR_TYPE_ID_pk source_column: addr_type_id target_column: ADDR_TYPE_ID
  • cast: null field_alias: LANG_CD_pk source_column: lang_cd target_column: LANG_CD
  • cast: null field_alias: ADDR_TYPE_TEXT_pk source_column: addr_type_text target_column: ADDR_TYPE_TEXT random_row_batch_size: '1000' schema_name: DB2I0 table_name: TPY_ADDR_TYPE_DESC target_schema_name: dp-dev-derived-ea49.product_ref_geographic target_table_name: address_type_desc threshold: 0 trim_string_pks: false type: Row use_random_rows: true

[helensilva14]

Hi @manojredward! What is the version of your DB2 instance? The error is indicating it could not found a RANDOM function to use without parameters being given (https://www.ibm.com/docs/en/db2/9.7?topic=messages-sql0250-sql0499#sql0440n).

I checked in our DVT test instance (LINUXX8664 11.5.8.0) and it works this way by using a default seed number, according to the documentation (https://www.ibm.com/docs/en/db2/11.5?topic=module-random-function).

But as we searched from version 11.1 and before, the function requires a seed number as parameter:

• 11.0: https://www.ibm.com/docs/en/db2/11.1?topic=SSEPGG_11.1.0/com.ibm.dwe.cubemdx.doc/mdx_random.htm
• 10.5: https://www.ibm.com/docs/en/db2/10.5?topic=functions-random-function-mdx

If that's your case we indeed need to make a code change, thanks for reaching out!

[manojredward]

Thanks @helensilva14 for your response. I will check the DB2 version and get back to you. Meanwhile, Could you please guide me how to pass the seed value in YAML file. Right now, we are passing something like mentioned below,

random_row_batch_size: '1000' type: Row use_random_rows: true

[helensilva14]

As of now, it is not possible to pass a seed number either using DVT CLI or YAML config file.

We'll work on an initial version of a code change to pass a seed direct in our code and ask your help to test it soon!

[manojredward]

Hello @helensilva14 We are using the below DB2 version, could you please check this? AIX v11.5.9.0 Mainframe V12R1M505

[helensilva14]

Hi @manojredward! Can you please try to execute the following SQL queries in your instance and let us know if they run without errors? Below you can see the output in our DB2 instance (LINUXX8664 11.5.8.0):

• Without seed (using default value): db2 "select random() from sysibm.sysdummy1;"
• With seed value: db2 "select random(2) from sysibm.sysdummy1;"

[manojredward]

Thanks @helensilva14, I will check and let you know asap. We are using AIX v11.5.9.0 DB2, hope this is fine. If we need to install any packages, please let us know.

[manojredward]

Hi @helensilva14, We tried to run the below queries in our AIX DB2 and got an error as SQL Error [42884]: NO AUTHORIZED RANDOM NAMED HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.33.31

Attached image for your reference

[helensilva14]

Hi @manojredward, thanks for sharing the error message and the images!

We did more research and might be the case that your instance/flavor only recognizes the function as RAND(), such as in this example: https://db2-sql-cookbook.org/#_getting_n_random_rows

Could you please try to execute the following query versions and let us know if they work?

• select rand() from sysibm.sysdummy1;
• select rand(2) from sysibm.sysdummy1;

[manojredward]

Hi @helensilva14 We have tried with rand() command and it is working as expected in our DB2. Please find the screenshot below.

[helensilva14]

Great to hear it! We updated our code to always use RAND() since this method is always available in all DB2 versions, which is not the case for RANDOM().

[manojredward]

Great, Thanks, Please let us know once the code is merged, we will recreate image and do our testing.

[helensilva14]

We merged the fix a few minutes ago, so it is available at the develop branch now. Can you try packaging the latest develop branch and testing? Please follow these steps:

1. Clone our DVT GitHub repo: git clone https://github.com/GoogleCloudPlatform/professional-services-data-validator.git
2. Enter in the folder and activate a Python virtual environment
3. Package the develop the branch with pip install . to package the current directory with the fix.
4. Test out the validation commands.
5. Install missing libraries, if needed

[manojredward]

@helensilva14

By following the thread, we were actually cloned the code base from branch: 1239-random-function-error-luw-db2 and created an image and executed our validation and we are getting the below error.

Error (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Binding Error: [IBM][CLI Driver][DB2] SQL0440N No authorized routine named "SETSEED" of type "" having compatible arguments was found. SQLSTATE=42884 SQLCODE=-440

We have added the below changes to our yaml file, random_row_batch_size: '2' use_random_rows: true

Could you please take a look on seed function and let us know the updates.

[helensilva14]

Hi @manojredward, thank you very much for your testing! I didn't face this error in our instance.

Anyways, I added a new line to our code to always set a default seed value before using the RAND() function, you can check the new pull request here -> #1249

Could you please repeat the steps and test again the branch 1239-random-function-error-luw-db2?

[manojredward]

Thanks @helensilva14 for making those changes, I will recreate the image with this branch and test.

[manojredward]

Hi @helensilva14, We have tested the code from issue1239-random-function-error-luw-db2 branch with two yaml's and both are give me the same error.

YAML #1: random_row_batch_size: '2' type: Row use_random_rows: true

YAML #2: random_row_batch_size: null type: Row use_random_rows: true

Error: 09/03/2024 12:48:33 PM-ERROR: Error (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Statement Execute Failed: [IBM][CLI Driver][DB2] SQL0583N The use of routine or expression "RAND()" is invalid because it is not deterministic or has an external action. SQLSTATE=42845 SQLCODE=-583

[helensilva14]

Hello @manojredward, thanks for reporting this new error!

I tried a new approach of setting the seed value for the RAND() function itself and not externally.

Could you please repeat the steps and test again the branch issue1239-random-function-error-luw-db2?

[manojredward]

Hi @helensilva14, Please find the below error when we test with the updated code from issue1239-random-function-error-luw-db2.

Error: 09/05/2024 06:49:34 AM-ERROR: Error (ibm_db_dbi.ProgrammingError) ibm_db_dbi::ProgrammingError: Binding Error: [IBM][CLI Driver][DB2] SQL0418N The statement was not processed because the statement contains an invalid use of one of the following: an untyped parameter marker, the DEFAULT keyword, or a null value. SQLSTATE=42610 SQLCODE=-418

YAML: random_row_batch_size: '2' type: Row use_random_rows: true

[manojredward]

Hello @helensilva14 and @luispavaogoogle Just want to check, do you have any updates for me?