Topic
IC4NOTICE: 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.
6 replies Latest Post - ‏2011-08-31T11:29:08Z by SystemAdmin
SystemAdmin
SystemAdmin
203 Posts
ACCEPTED ANSWER

Pinned topic Design Studio Control Flow Migration

‏2011-08-17T08:17:00Z |
Hello.
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...
Thanks,
Aleksandar

Old platform:
DWE Design Studio Version: 9.1.3
IBM DB2 8.2
AIX64
WebSphere Application Server 6

New platform:
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 7.0.0.3
JDBC DRIVER FOR DB2 ver. 3.58.82
Updated on 2011-08-31T11:29:08Z at 2011-08-31T11:29:08Z by SystemAdmin
  • JP_Parkin
    JP_Parkin
    128 Posts
    ACCEPTED ANSWER

    Re: Design Studio Control Flow Migration

    ‏2011-08-18T14:03:30Z  in response to SystemAdmin
    hi 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.

    Best regards,
    JP
    • SystemAdmin
      SystemAdmin
      203 Posts
      ACCEPTED ANSWER

      Re: Design Studio Control Flow Migration

      ‏2011-08-22T10:04:51Z  in response to JP_Parkin
      Hi 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?
      Thanks again!
      Aleksandar
      • SystemAdmin
        SystemAdmin
        203 Posts
        ACCEPTED ANSWER

        Re: Design Studio Control Flow Migration

        ‏2011-08-22T10:27:41Z  in response to SystemAdmin
        Hi 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...
        • SystemAdmin
          SystemAdmin
          203 Posts
          ACCEPTED ANSWER

          Re: Design Studio Control Flow Migration

          ‏2011-08-22T10:28:52Z  in response to SystemAdmin
          ... and here is second file, execution log.
          Thanks.
          Aleksandar
          • JP_Parkin
            JP_Parkin
            128 Posts
            ACCEPTED ANSWER

            Re: Design Studio Control Flow Migration

            ‏2011-08-24T12:55:38Z  in response to SystemAdmin
            Hi 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.

            Best regards,
            JP
            • SystemAdmin
              SystemAdmin
              203 Posts
              ACCEPTED ANSWER

              Re: Design Studio Control Flow Migration

              ‏2011-08-31T11:29:08Z  in response to JP_Parkin
              Hi 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...

              Aleksandar