Hello Power BI folks,
I have an issue trying to work with a multimember file within DB2 on an IBM i (sometimes referred to as iSeries or AS/400). We are retrieving data from a multimember file that is part of a third party product used for system heatlh metrics. Multimember files are proprietary to DB2 for i OS and not known to SQL implementations. So, by default when you access a multimember file SQL will default to the *FIRST member in the file which is the oldest data. Making things more difficult is the fact that the member name is not consistent. It starts Q<julian day of year>00003...0004......0005. One member is created for each julian day of year. Here is the setup of the file we query for CPU statistics QAPMSYSL:
Member List
Q280000104 *first member (oldest)
Q281000103
Q282000103
Q283000104
Q284000104
Q285000104
Q286000105 - *last member (current data)
There are some workarounds to set the member accessed by SQL to *LAST member Q286000105 which is the current data. IBM details them in a support article: http://www-01.ibm.com/support/docview.wss?uid=nas8N1018261
CALL QSYS.QCMDEXC('OVRDBF FILE(file) TOFILE(library/file) MBR(member) OVRSCOPE(*JOB)', 0000000065.00000)
our attempts to use the CALL syntax above will fail with Power BI while using the IBM ODBC driver. It seems Power BI does not support this function at this time. Is it possble to provide a way to call an external procedure from within Power BI?
Are other options are not as elegant.
We could use an ALIAS to create another file based on the *LAST member but since we do not know the member name we have to add logic to retrieve that and then create the alias file every day.
Write a RPG program to merge all members of the file into one read-only looking file every day.
sample here:
https://www.itjungle.com/2016/03/01/fhg030116-story01/
I guess I am looking for the easiest way to allow us to always capture the data from the current member for the SQL to run properly without having to do much coding. Any ideas?
Jim O.
