Slow DB2 refresh Power BI Desktop

[GTech01]

Hi,

Since December update we have hard time refreshing data with our DB2 database. Previously we were able to refresh around 7000 records a second, now the first 100-150 000 records are relatively OK but after that the pace drop at around 100-200 records a second.

Thanks

[BjoernSchaefer]

Hey @Anonymous,

are you using a gateway to refresh the Datasets in the Service? If so, wich Version are you using? I'm using the December '18 Version of the Gateway wich contains the same Mashup Engine as in PBI Desktop '18/February '19. That leads to a timeout during the scheduled refreshes in the Service. The Refresh runs two hours and then cancels.

Regards

BjoernSchaefer

[Anonymous]

Sept. 18.  But with that being said, I am getting one report now that has timed out two nights in a row.  I need to dig into that report a little further, it may be a server issue as all other reports (and large dataset reports) are not failing.

[BjoernSchaefer]

@Anonymous, the other reports are not failing because the Gateway uses an "older" Version of the Mashup Engine. If you install the December '18 Version of the Gateway and you own Reports, that take longer than 2 hours in PowerBI Desktop the refreshes in the Service won't finish. So, the best you can do, and i envy you for that, is to stay at the Sept'18 Version of the Gateway as long as possible.

But for that one Report you mentioned, yes, dig deeper. When you use a merge over large tables... well, this is a performance killer.

Regards

BjoernSchaefer

[Anonymous]

Are you changhing the names of the headers programatically in your DB2 tables?

[BjoernSchaefer]

@Anonymous, i'm not certainly sure what you mean. I don't do programming on the DB2 Database, all i'm doing is to get data from the source and when i know, wich columns and what kinds of attributes i need from a table i write a SQL-Statement. 

In the SQL-Statement i trim Textfields like trim(textfield) as newtextfieldname and so on. Datefield for example are split into three columns. I merge these 3 columns into one Textfield via a SQL-Statement and convert it into a Datefield.

SQL-Statement: select trim(column1) as column1, trim(column2) as column2, date(cast(Day as varchar(2))||'.'||cast(Month as varchar(2))||'.'||cast(Year as varchar(4))) as Date, Num-Column from schema.table where condition1=whatever your condition is

The Date-Conversion can lead to errors because, in our system, the Datefield is manually filled. And when humans fill fields manually, they can enter non-plausible Datevalues. In that case, you'll get an error like "Decimals out of range" or something like that. Then, i don't use the DATE-Function in the SQL-Statement. That leads to an Text-Field called DATE wich i convert to Date-Type in PowerQuery and remove the Errors. 

I hope this is sufficient enough for you and hopefully i answered your question correctly. 

Regards

BjoernSchaefer

[Anonymous]

Okay.  I have maybe noticed something and maybe not on my end (but if you are not doing this and still slow then it really doesn't makes sense).  When i would bring in a table i had it in two steps. for this example i will say:

select * from library.file

this brought in the Upper case unfriendly names

then run query that saved proper case friendly names from system database

i merged those two in PBI so I didn't have to rename a bunch of columns.  The DB2 importer for dot net would not work to bring in the friendly names as it displayed those friendly names with Chinese characters. 

Your post then clued in me in to try using ODBC for those friendly names and it worked perfectly.  My refreshes now seem a little quicker to start using the ODBC connection for the table of friendly names.

Now, in writing this all out, I think i should i switch all my load queries to dynamic queries and change the names at that time versus loading two tables in PBI and programmitly changing names.

Example of renamed headers 

PYMNT_Table = DATA_Schema{[Name="PYMNT",Kind="Table"]}[Data],
#"Renamed Columns" = Table.RenameColumns(#"PYMNT_Table",Table.ToRows(PYMNT_headers))
in
#"Renamed Columns"

[BjoernSchaefer]

@Anonymous,

did you manage to get the IBM-Driver working instead of the .NET-Driver?

Regards

BjoernSchaefer

[Anonymous]

no. i have not.  have you?

[BjoernSchaefer]

@Anonymous,

i searched and searched the Internet, but i guess that no one actually tried to use the IBM Driver. What i figuerd out was the following:

1. When you select the IBM Driver you'll get an Error saying that Clientsoftware is missing and i have to install the IBM DS Driver Packcage. So, i did.
2. I then selected the IBM Driver again. Error Message: No Driver like IBM.Data.DB2 found.
3. I then installed the IBM Data Server Client wich you can get from the same Page as the DS Driver Package. It contains some additional tools like a DB2-Command Shell and so on.
4. After the installation of the Data Server Client, you'll see a new Entry in the Startmenu of Windows 10 called "IBM DB2 DB2COPY1 (Standard)".
5. It contains a wizard where you can set the standard DB2-Copy. It's pretty simple. All you do is to choose the Driver and what then follows, i can only quess.
6. After you set the Standard DB2-Copy, the connector in PBI-Desktop behaves a little different.
7. Before that, i choose .NET-Driver and have to enter the IP of my DB2-Database with Port 446. I don't know why, but it worked.
8. After the insatllation, i'm choosing the IBM-Driver. I still get a Error-Message but a different one. It's telling me, that the Communication Protocoll chosen is TCPIP but the actual Prototcoll is Sockets. SQL State 08001

I can provide the Error-Message only in German, but maybe you can get a clue on what's going on.

Details: "IBM DB2: ERROR [08001] [IBM] SQL30081N  Kommunikationsfehler. Verwendetes Kommunikationsprotokoll: "TCP/IP".  Verwendete Kommunikations-API: "SOCKETS".  Position, an der der Fehler erkannt wurde: "192.168.220.7".  Übertragungsfunktion, die den Fehler festgestellt hat: "connect".  Protokollspezifische(r) Fehlercode(s): "10061", "*", "*".  SQLSTATE=08001 

I was searching the IBM-Support for a conclusion and all i found out was, that it will MAYBE a problem with the Port 50000. 

In an other Post someone mentioned, that i have to set the protocoll to TCPIP with the command db2set db2comm=tcpip. But the error still occures.

That's Status Quo. I'm still trying to get it running.

Regards

BjoernSchaefer

[Anonymous]

will try to work to dig into your error