We are in process of moving dw applications from old platform (description below) to new (description below). To be precise, we exported Control Flows and Data Flows from old platform and imported them in new (as in, the whole Projects), and from there we are creating dw applications to put onto new app server, since we are physically changing dw database server, along with app server. In that we encountered a problem with one of them; Old version is working ok, but on new platform it doesn't, with the message:
Call ADMIN_CMD: SQL Exception. SQL state = 54002; error code = -102;
error Message = The string constant beginning with "..." is too long.
SQLCODE=-102, SQLSTATE=54002, DRIVER=3.58.82
We saw that the generated sql is larger than what is allowed on database server but what puzzles us is how it worked on old platform?
On new platform it works when it is started manually through Design Studio, so DS has a different way of generating sql statement for DS execution than for app server application.
Is there some way to make this work?
Are there some parameters to consider or change in DS or App server?
Can that sql statement can be in some way altered on app server (if that's even possible)?
Any feedback is appreciated. Also, if I can provide you with some more info, just say so...
DWE Design Studio Version: 9.1.3
IBM DB2 8.2
WebSphere Application Server 6
IBM Infosphere Warehouse, Design Studio Version: 9.7.2
IBM DB2 9.7.2
SUSE Linux 10, 64 bit
IBM InfoSphere Warehouse 9.7
WebSphere Application Server 188.8.131.52
JDBC DRIVER FOR DB2 ver. 3.58.82
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
6 replies Latest Post - 2011-08-31T11:29:08Z by SystemAdmin
Pinned topic Design Studio Control Flow Migration
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2011-08-31T11:29:08Z at 2011-08-31T11:29:08Z by SystemAdmin
JP_Parkin 100000R2WA128 PostsACCEPTED ANSWER
Re: Design Studio Control Flow Migration2011-08-18T14:03:30Z in response to SystemAdminhi Aleksandar,
The SQL and commands that were generated in the 9.1.3 may be different from the SQL and commands generated in the 9.7.x release. In the 9.7 version we re-designed the optimization logic for the SQL Warehousing Tool (SQW) and as such the generated code may look different, even though it will accomplish the same transformations in the end. For example, there were improvements made to not only push predicates down to the source tables, but to be able to push joins down as well in order to reduce the number of rows that need to be pulled from a source database.
For your situation, my guess is that there may be a variable involved in this transformation that might be triggering a difference in behaviour between the Design Studio and the Admin Console ( but that really is a guess ).
To help diagnose the problem, there's a couple things you can provide :
1) The data flow from the Design Studio that is encountering the problem
2) The full log file output from the Admin Console - make sure that you set TraceLevel=Both in the Control Flow management ( from the Manage Control Flows tab, click on the name of the control flow via the hyperlink. Choose the Logging tab from the Control Flow Details pop-up window and change Trace Level to Both, then click Apply to save the setting ).
These two items should give us enough information to figure this out - if there are variables involved, please let us know the values.
If you are not comfortable putting this information in the forum, it would be appropriate to open a PMR and provide this information to the IBM support team and we can help you out via that method.
Re: Design Studio Control Flow Migration2011-08-22T10:04:51Z in response to JP_ParkinHi JP,
and thanks for fast reply.
These days we examined the situation a little bit closer.
There is one variable that's being used in that critical Data Flow; it's just database connection, but we'll try anyway to change that and not use it, and see if it will make some difference.
Also, Data Flow is ready to be sent, but full log file that's generated is about 900 MB (?!). Section that refers to critical Data Flow is small enough and it can also be sent, but there is just matter of company's security policies, so I'll have to get back to you about that.
Last, if we decide to share info on forum, it will be presented through two files, sizes around 120 and 150 KB, I hope that's ok?
Re: Design Studio Control Flow Migration2011-08-22T10:27:41Z in response to SystemAdminHi JP.
After a short staff consultation and omitting database IP address (I think it's all the same, with or without it), I'm going to put compressed Data Flow files and piece of log that refers only to critical Data Flow execution (log trace level set to Both). Regarding variable that's being used, as I said, it's database connection, if real values could give you some more info, let me know, and I'll provide it.
Since I can upload just one file per post, here's first, Data Flow files...
JP_Parkin 100000R2WA128 PostsACCEPTED ANSWER
Re: Design Studio Control Flow Migration2011-08-24T12:55:38Z in response to SystemAdminHi Aleksandar,
I managed to get your data flow working in my test environment ( with empty table definitions ). The error that you are getting in the Admin Console should occur regardless of whether there is data to be processed. When I deployed the application to the Admin Console, the flow also works there - no sqlcode=-102 error was reported.
So I think your next steps for diagnosing this issue would be the following :
1) Confirm that the connection you are using in the Admin Console matches the Design Studio
2) Provide your application zip file as an attachment to the forum - maybe there's something in that generated file that is triggering the problem.
Beyond those suggestions, I think it makes sense to open a PMR with the IBM support team in order to dig into this some more. We'd likely start with a JDBC trace of the application from the Admin Console to figure out whether this is an application or database issue. From there we can engage more experts to help solve this issue.
Re: Design Studio Control Flow Migration2011-08-31T11:29:08Z in response to JP_ParkinHi JP.
Thanks again for the effort. Nudged by Your test results we came up with idea that re-creating Data Flow, instead of (just) importing it, could potentially resolve issue. So, first try made error -102 disappear. But, it gave us some other error that's in process of resolving. But, it seems that it could be some progress in our process of migration.
Also, PMR could in fact be the proper thing to do right now, so some of my colleagues are on top of that.
So, thanks again for the input and testing, and, if it could mean anything to You, I'll post results from our additional testing and migration here when there's something to post...