We’re trying to do an upgrade from 11.7 (yes, I know) to 4.1 and are running into a problem with some dataflows that include pushdown_sql, which is used to do an in (xxx), where xxx is a comma-separated list loaded from a global variable.
Even though the pushdown_sql is in the transform that directly connects table data sources, and a validate completes successfully, we get an error in runtime OR “view optimized SQL” that the pushdown_sql is illegal - about two query transforms down the line. So far, the only way around this issue is to “fool” the optimizer by putting a Data_Transfer after the first step or giving it conflicting sorts to do. For some reason, at that point, though, sometimes the engine throws an “internationalization error” that definitely wasn’t there in 11.7 either. I’m attaching an illustration of the issue.
Given where DS is saying the error is, I suspect that DS is trying to condense all of your Query transforms together and push them all down to the database. Sometimes it is smart enough to do that.
Try placing a Merge transform before the Query transform where the error is occurring. This is similar to using a Data_Transfer transform to fool DS.
Alternatively, use a SQL transform and be done with it.
You have two different datastores for the join at ‘qry_Extract_Customer_Data’. Also the “Display Optimized SQL” never works with the PUSHDOWN_SQL() in any where clause. Just comment that statement and then display the Optimized SQL and then add it back.
Another option would be, add an additional query transform to the table for which the PUSHDOWN_SQL() is applied. I do the exact same scenario as you mentioned, however in my case there is one source dataflow and hence the complete source join along with the WHERE clause is pushed down.
Also let us know what the runtime error is? Is that the same as the one you displayed?
Pushdown_sql() does show up in Display Optimized SQL. I am looking at a DS 12.2.3.3 Dataflow that has a hard coded string in a pushdown_sql() and that hard coded string does show in the Display Optimized SQL. If the pushdown_sql() uses a variable then (as I recall) it will show the variable name in the Display Optimized SQL.
Runtime error is the same. Put the merge transform after the first query. We’re now getting an “internal internationalization error” thrown by the engine itself, even though everything is on the same codepage:
I haven’t ever encountered the Internal internationalization error before. It could be a codepage problem so check to make sure you have your code pages matching. Are you using some of the *_EXT functions? I think those address codepage variations in data.
Are you running the latest patch for DS 4.1? If not, give that a try. If it was me, I would be upgrading to 4.2, not 4.1.
ADAPT01693724
Data Services job containing a SAP source/target with double data type may fail with an error message "Internal internationalization error <0,U_ILLEGAL_ARGUMENT_ERROR>, function <107>, locale <eng_us.utf-8>, GlobalC locale <en_US.UTF-8>". This issue has been fixed in this release - 14.1.1.392
Yes, 4.1 SP3 released on 12/31 Funny thing is, the release note isn’t released yet!!! I have a case for this !!!
Let us get back to the issue though… If you will use SMTP_TO() and getting this error, then one case for support !!! However, just comment and proceed with the actual issue
Right now, we’re testing with the DF in question isolated into its own job, with the appropriate global variables set. So, there’s no smtp_to() in the job.
(BOB member since 2007-09-12)
(BOB member since 2008-07-17)
Funny thing is, the release note isn’t released yet!!! I have a case for this !!!