Oracle Query is not working in the workflow

I have a series of queries that I put into a script, and they work fine in a dataflow.
example:
=====================
truncate table LPCOMT2;
insert into LPCOMT2
select LPACNR
,listagg (LPCOMM, ’ ') within group (order by lpseq_) as LPCOMM
from coreconv.LPCOMT;
UPDATE LPCOMT2 SET LPCOMM = REPLACE(LPCOMM, CHR(13), ’ ');
UPDATE LPCOMT2 SET LPCOMM = REPLACE(LPCOMM, CHR(10), ’ ');
commit;
===========

But this is not working in the workflow. It gives the error “ORA-00911: invalid character” which I suspect is because of all the semi-colons. I tried using different Query Batch Terminators such as “;”, “/” and blank, but none of them worked and resulted in the same error.

I also tried adding a BEGIN and END; to the beginning and end of the query (with the addition of “execute immediate” to the truncate because it’s not supported.), but it broke my SQL query stand-alone with a “PLS-00103: Encountered the symbol "end-of-file” error.

When I tried it on the workflow, it only worked once. I ran it again with no changes and I got this error

ORA-01013: user requested cancel of current operation
ORA-06512: at line 26

Please let me know what is going on as soon as possible.

How are you running the script in a dataflow?

I put the following in a workflow and it only works sometimes (hit or miss) and it feels like a timeout issue:
-------------------------------------------
truncate table ssxref2;
insert into ssxref2
select
XDELE,
XSSNP,
XACNR,
XCKNR,
XLNAM,
XCDAT,
XACTC,
XTLN2,
listagg (XREMK, ’ ') within group (order by rownum) as XREMK2
from
coreconv.ssxref
where xcdat > ‘01-MAR-05’
group by XDELE, XSSNP, XACNR, XCKNR, XCDAT, XLNAM, XACTC, XTLN2;
commit;

I looked at your help menu and it was interesting that there’s a timeout button missing from our version of Centerprise:
You have under Tools>Options> General, there are two selections, Max result rows and Connection Timeout (Seconds):

But we have only Max result rows:

image

You’ll find the connection timeout setting in the Advanced section for the
connection:

Changing the timeout made the SQL Query run all by itself. We are having issues with adding simple update statements. Where and how do you suggest we run these in a workflow? Nothing seems to work using SQL Query with both Oracle and SQL servers. We keep getting syntax errors with the Query Batch Terminators, and what the tool is expecting. How do you run a simple truncate table in both Oracle and SQL servers as part of a dataflow?

Also, If I create a simple dataflow and start it off with a SQL Query Source, and increase the timeout parameters, it seems to go off somewhere and get stuck, and never come back. The query is:

select min(prodfiles.CMDYtodate(PHTRDT)) as FIRSTDUEDATE,
PHACNR,
PHLNUM
from CNVLIB.PHFILE
WHERE rtrim(ltrim(PHTRCD)) = ‘C7’
group by PHACNR, PHLNUM

It’s not a complicated query and takes a while to come back in DB2 Navigator, but it does have output rows.

My questions are:

  1. How can I figure out that my new server version is working correctly? Some dataflows work and others don’t.

  2. When I did run it standalone, I could see in task manager the resources needed for the dataflow, but when I’m on the server, there’s not much of a difference when I run my dataflow.

  3. I’m not even at the point of running my dataflow, this is just clicking the “next” when I plug in my SQL on the SQLQuerySource1: Source Fields within the dataflow:
    image

This will never come back.
Thanks for any help you can provide.

Hi,

  1. When it comes to running the actual dataflow, it should not just hang there forever while running the query. If it does, there is something wrong. And there is no difference in the code base between the stand-alone and the server versions. If it works in one, it should work in the other.

  2. When looking in the task manager, be sure to look under Astera.TransferService.exe and not Centerprise5.exe as you would in the stand-alone version.

  3. I would modify the SQL query so that it returns no data (i.e., append something like where 1 = 2 or something that will not produce any records). The reason for this is that we don’t want any data, in this case, we just want the schema of the returned dataset. Once you’ve built the layout, remove the extra part of the query.

Try with this and if you’re still having problems, let me know.

Thanks for the reply, and I got excited for about 5 seconds when I was able to add ‘FETCH FIRST 2 ROWS ONLY’ to the SQL statement and it returned the output. When I set my schema of the returned dataset, and try to change it by taking out the FETCH statement, it goes off again and becomes completely unresponsive.
It was interesting that when I tried the following:
FETCH FIRST 100 ROWS ONLY – 7 seconds to respond
FETCH FIRST 500 ROWS ONLY – 21 seconds to respond
FETCH FIRST 800 ROWS ONLY – 32 seconds to respond
FETCH FIRST 1000 ROWS ONLY – 10 minutes and counting, will not come back
Do you think there’s some parameter that I’m not thinking about? Looks like a limitation somewhere. This table contains about 8.6 million rows that it is trying to group together to get a max value, so it is very sort intensive. Let me know how you suggest we proceed.

I know we recently made a change that did not require the layout to be built when changing the SQL Query source query. What is your current version of Centerprise?

Version 5.1.281.0
I’m currently dumping the db2 data to the SQL server to see if I can do a max/group by query there.

Hi,
Try downloading the build that I have emailed. It will fix the layout issue.

Now any changes to working df’s do not work (even the adding of a comment or adding a single space) gets the following error:
Error running SQL statement <–select min(prodfiles.CMDYtodate(PHTRDT)) as FIRSTDUEDATE,
– PHACNR,
– PHLNUM
–from CNVLIB.PHFILE
→ to build layout. SQL0104N: Token - was not valid. Valid tokens: (END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER BEGIN.

If I create a new dataflow, with my query to fetch 10 rows I get the following error:

Error running SQL statement <select min(prodfiles.CMDYtodate(PHTRDT)) as FIRSTDUEDATE,
PHACNR,
PHLNUM
from CNVLIB.PHFILE

to build layout. SQL0122N: Column PHACNR or expression in SELECT list not valid.

My DB2 query:
select min(prodfiles.CMDYtodate(PHTRDT)) as FIRSTDUEDATE,
PHACNR,
PHLNUM
from CNVLIB.PHFILE
WHERE PHTRCD = ‘C7’
group by PHACNR, PHLNUM
fetch first 10 rows only

I have emailed the build that has both the SQL Query Source issue and the connection timeout issue fixed for workflow.