Egrodrigues2014 Tracker

Re: SET BULKLOAD OPTIONS (POSTGRES ENGINE) TO SCD2 TRANSFORMATION IN DIS

Egrodrigues2014 — Tue, 01 Nov 2022 16:31:09 GMT

We need to load the historical records and this data volume will be only in the first iteration.

As you can see, we change the option "Upload library before targe table update" to EXT_VI library. But the "etls" tables still in work library.

/* Delete the work tables  */ 
      proc datasets lib=EXT_VI nolist nowarn memtype = (data view);
         delete "W6B3TJ5F"n;
         delete "W6B3TIUB"n;
         delete "W6B3TIOR"n;
      quit;   
     
         proc datasets lib=work nolist nowarn memtype = (data view);
            delete etls_newrcds;
            delete etls_match;
            delete etls_close;
         quit;

To load data from work to postgres tables, we need to set bulkload.

Is there a way so that these tables are also generated in the "EXT_VI" (postgres) library ? Thus, passthrough can be performed without the need for BULKLOAD.

We set "Libref to copy temporary work tables to" SCD option to EXT_VI, but it does not work.

Thanks in advance.

SET BULKLOAD OPTIONS (POSTGRES ENGINE) TO SCD2 TRANSFORMATION IN DIS

Egrodrigues2014 — Sun, 30 Oct 2022 19:49:47 GMT

Hi everyone,

When I set BULK LOAD option as active, SCD2 transformation create the following code (for ETLS_CLOSE as example):

      proc append base = EXT_VI."W6B3BTSM"n
         (
            BULKLOAD=YES
         )
         data = work."ETLS_CLOSE"n force; 
      run;

But this code doesn't work. I need to set "bl_psql_path='/PATH' " and "bl_delete_datafile=no" dataset option to BULKLOAD works properly.

I aware that I can change the automatic generated code to add these options.

Is there a way to configure these options at the transformation level? Or a way that I can add these options without change the code manually?

Thanks in advance.

Re: Copy a work.table to POSTGRES database is taking a long time

Egrodrigues2014 — Wed, 26 Oct 2022 09:31:50 GMT

Hi @Tom , thanks for the suggestions.

I am using POSTGRES and ODBC engine to connect to POSTGRES and SQL Server respectively. These products are licensed.

Copy a work.table to POSTGRES database is taking a long time

Egrodrigues2014 — Tue, 25 Oct 2022 14:18:39 GMT

Hi experts,

We are trying to copy a work table (72 millions obs) to a POSTGRES database and it is taking a long time to run. We had a similar issue to copy a huge work table to SQL Server database and we solve it put INSERTBUFF options (32767) at library level.

Down below I share the comparison between copying a work table to SQL Server (we already have it optimized) VS copying it to POSTGRES database:

Postgres execution (STG_PG.PRUEBA3):

SQL Server execution (staging.PRUEBA3):

At the odbc.ini level this is the configuration for SAS/ACCESS Interface to PostgreSQL:

Finally, we execute the following code (same as before) with BULK_LOAD, BL_PSQL_PATH and BL_DELETE_DATAFILE at dataset options level and real time decreased:

Is there a way to configure these options at the library level (BL_PSQL_PATH and BL_DELETE_DATAFILE)? How can I set these options so that BULK LOAD is always active?

Please, if anyone has faced this problem before or knows what is missing, please, let us know. It will be more than welcome.

Thanks in advance.

Re: Real time higher than USER and SYSTEM CPU times combined.

Egrodrigues2014 — Fri, 14 Oct 2022 12:24:40 GMT

Hi @Sajid01 ,

We are aware that could be a due to network congestion issue. We asked customer about any change in network traffic, and they said still the same.

As it executes as batch process in production, which runs at night (approx 3:00 am), we cannot monitor it at the moment using OS tools (Windows Server 2012 R2). Is there a way to save the historical information of I/O disk and network usage during batch execution? It is a customer's server and we do not have permission to install any monitoring software.

Re: Real time higher than USER and SYSTEM CPU times combined.

Egrodrigues2014 — Fri, 14 Oct 2022 12:05:15 GMT

Hi @ballardw ,

To be more specific, this is one of the queries that have increased in time dramatically. As you can see, it is reading from a data base (SQL Server, VAULT schema) and work and writing in work directory.

Number of observations its almost the same:

We asked customer about any change in network traffic, and they said still the same.

Real time higher than USER and SYSTEM CPU times combined.

Egrodrigues2014 — Thu, 13 Oct 2022 20:19:04 GMT

Hi Experts,

We have a batch process that executes every day. This week, a job that usually does not past 18 minutes of execution time (real time, as you can see), now is taking more than 45 minutes to finish.

We already have Fullstimmer option active, but we don't know why only the real time was increased and why only this week.

It might be an I/O issue. Does anyone know how we can identify if it is really an I/O problem or if it could be some other issue (network, for example)?

In old documentation there are Fullstimmer stats that could help to identify the problem but they do not appear in log. The stats are those down below, as you can see:

Please, if anyone has faced this problem before or knows what is missing, please, let us know. It will be more than welcome.

Thanks in advance.