Skip to Content

We have an BODS ODBC to many open connections issue.

BODS ODBC issue in that the connection to database should only create one connection for many BODS SQL statements yet one connection (500+) is created per SQL statement.

Testing this job design in the same version and tracing the database did show 500 sql script ran against the database with the same ClientprocessID and SPID. Although we only should see ONE connection per job.

These threads are from the Data Services Engine which is creates the connection to the database until the job is completed, with the same Client process iD and Session pid it really is just the one connection.

Researching sql commands to close the connection returned alter database commands and kill pids which would kill the entire process or lock the database on the next run.

Anyone know of an internal property or ODBC setting we can use to make only once connection per BODS job versus many? Thank you in advance.

The issue with many connections created per BODS job using an ODBC connection is that the maximum number of database connections is exceeded (500). Other BODS jobs against other databases ONLY create one connection. We are using ODBC connection.

Add comment
10|10000 characters needed characters exceeded

  • I'm seeing the same issue with Postgres as the target....A simple select via the sql() function in a loop causes a unique connection the backend postgres database per each sql() call, and it keeps it open (does not disconnect until the JOB completes)...leaving 500 unique connections/sessions on the database.

    $G_NT = 1;

    WHILE ($G_NT < 500)

    begin

    $G_FETCH = sql('DS_ODBC', select count(*) from myschema.foo;');

    $G_NT = $G_CNT + 1;

    end

    Why does BODS need to keep the 500 DS_ODBC connections open within the Job?

    I need it to 1) disconnect after each sql() call or 2) use the same database connection for all calls within the job.

  • Get RSS Feed

1 Answer

  • Apr 25 at 07:27 AM

    The problem is resolved.

    THE PROBLEM: Found this SAP setting in ds_odbc.ini but NOT in .odbc.ini file.

    NoteRebrandedLib = TRUE is required when using the SAP rebranded Data Direct driver

    THE SOLUTION:

    NoteRebrandedLib = FALSE This is a postgres odbc driver, not an SAP Data Direct driver

    EXTRA CREDIT:

    We ran with 5,000 queries in 4 minutes against 50billion records with no error.

    Add comment
    10|10000 characters needed characters exceeded