SELECT via JDBC: Internal error in SQL ENGINE at 1200...

Mykimus

Mykimus

New Member
Hi, everybody.

I've got a SQL statement I'm trying to run against an OpenEdge version 10.2B database. I've tried this thing every way to Sunday, and thus far it's kicking my rear. I'm hoping someone has seen this error before and can point me in the right direction...because my Progress/OpenEdge support provider is being more of a hindrance than a help!

Here's the error I'm getting when running the select:

HY000:[DataDirect][OpenEdge JDBC Driver][OpenEdge] Encountered internal error in SQL ENGINE at 1200 in Z:/vobs_sql/sql/src/sts/sts_aggrun.cxx. Contact Progress Technical Support
Click to expand...

Now...here's the statement. (And if anyone knows of a better way to get what I'm after, I'm certainly open to suggestions...)

Code: 
SELECT
    CAST(SALES.company_no AS VARCHAR(10)) + '!' + UCASE(CAST(SALES.prod AS VARCHAR(20))) + '!' + UCASE(CAST(SALES.whse AS VARCHAR(10))) + '!' + CAST(SALES.yr AS VARCHAR(4)) AS "record_id",
    SUM(SALES.salesamt[1]) AS "jan_sales",
    SUM(SALES.salesamt[2]) AS "feb_sales",
    SUM(SALES.salesamt[3]) AS "mar_sales",
    SUM(SALES.salesamt[4]) AS "apr_sales",
    SUM(SALES.salesamt[5]) AS "may_sales",
    SUM(SALES.salesamt[6]) AS "jun_sales",
    SUM(SALES.salesamt[7]) AS "jul_sales",
    SUM(SALES.salesamt[8]) AS "aug_sales",
    SUM(SALES.salesamt[9]) AS "sep_sales",
    SUM(SALES.salesamt[10]) AS "oct_sales",
    SUM(SALES.salesamt[11]) AS "nov_sales",
    SUM(SALES.salesamt[12]) AS "dec_sales",
    SUM(SALES.cogamt[1]) AS "jan_cogs",
    SUM(SALES.cogamt[2]) AS "feb_cogs",
    SUM(SALES.cogamt[3]) AS "mar_cogs",
    SUM(SALES.cogamt[4]) AS "apr_cogs",
    SUM(SALES.cogamt[5]) AS "may_cogs",
    SUM(SALES.cogamt[6]) AS "jun_cogs",
    SUM(SALES.cogamt[7]) AS "jul_cogs",
    SUM(SALES.cogamt[8]) AS "aug_cogs",
    SUM(SALES.cogamt[9]) AS "sep_cogs",
    SUM(SALES.cogamt[10]) AS "oct_cogs",
    SUM(SALES.cogamt[11]) AS "nov_cogs",
    SUM(SALES.cogamt[12]) AS "dec_cogs",
    SUM(SALES.qtysold[1]) AS "jan_units",
    SUM(SALES.qtysold[2]) AS "feb_units",
    SUM(SALES.qtysold[3]) AS "mar_units",
    SUM(SALES.qtysold[4]) AS "apr_units",
    SUM(SALES.qtysold[5]) AS "may_units",
    SUM(SALES.qtysold[6]) AS "jun_units",
    SUM(SALES.qtysold[7]) AS "jul_units",
    SUM(SALES.qtysold[8]) AS "aug_units",
    SUM(SALES.qtysold[9]) AS "sep_units",
    SUM(SALES.qtysold[10]) AS "oct_units",
    SUM(SALES.qtysold[11]) AS "nov_units",
    SUM(SALES.qtysold[12]) AS "dec_units"
FROM
    PUB.SALES AS SALES
WHERE
    AND SALES.company_no = '1'
GROUP BY
    "record_id"

As is hopefully evident, I have ;-delimited fields of monthly sales, cost, and qty values...and I need to accumulate them by each unique company/product/warehouse/year combination. If I simplify to just pull the sales values, it works fine. Just the cost - no problem. Just the units - works great. Any two of those - usually works ok. (Sometimes I get a timeout, but usually goes through fine.) However, when I hit it with the whole papaya, the error mentioned above is thrown almost immediately.

Has anyone seen anything like this?

Much thanks in advance for any assistance -

Mykimus
 
Just my $0.02: the error says to contact PSC support, so that's what your application provider should do. Why would they push back on that?

I know that's not too helpful, but I would keep leaning on them until they open a support case.
 
don't see anything special in this SQL, beside all those cast to character and upercase calls just to form the 'record id'... I would instead get the data as is and do what ever transformation is required on the 'client' side (be it a UI or some reporting tool).

other than that try to group on columns that make up the 'record id'... and I've always though aliases are for lazy peoples like me to save some typing :)

Code: 
SELECT
    CAST(S.company_no AS VARCHAR(10)) + '!' + UCASE(CAST(S.prod AS VARCHAR(20))) + '!' + UCASE(CAST(S.whse AS VARCHAR(10))) + '!' + CAST(S.yr AS VARCHAR(4)) AS "record_id",
    SUM(S.salesamt[1]) AS "jan_sales",
    SUM(S.salesamt[2]) AS "feb_sales",
    SUM(S.salesamt[3]) AS "mar_sales",
    SUM(S.salesamt[4]) AS "apr_sales",
    SUM(S.salesamt[5]) AS "may_sales",
    SUM(S.salesamt[6]) AS "jun_sales",
    SUM(S.salesamt[7]) AS "jul_sales",
    SUM(S.salesamt[8]) AS "aug_sales",
    SUM(S.salesamt[9]) AS "sep_sales",
    SUM(S.salesamt[10]) AS "oct_sales",
    SUM(S.salesamt[11]) AS "nov_sales",
    SUM(S.salesamt[12]) AS "dec_sales",
    SUM(S.cogamt[1]) AS "jan_cogs",
    SUM(S.cogamt[2]) AS "feb_cogs",
    SUM(S.cogamt[3]) AS "mar_cogs",
    SUM(S.cogamt[4]) AS "apr_cogs",
    SUM(S.cogamt[5]) AS "may_cogs",
    SUM(S.cogamt[6]) AS "jun_cogs",
    SUM(S.cogamt[7]) AS "jul_cogs",
    SUM(S.cogamt[8]) AS "aug_cogs",
    SUM(S.cogamt[9]) AS "sep_cogs",
    SUM(S.cogamt[10]) AS "oct_cogs",
    SUM(S.cogamt[11]) AS "nov_cogs",
    SUM(S.cogamt[12]) AS "dec_cogs",
    SUM(S.qtysold[1]) AS "jan_units",
    SUM(S.qtysold[2]) AS "feb_units",
    SUM(S.qtysold[3]) AS "mar_units",
    SUM(S.qtysold[4]) AS "apr_units",
    SUM(S.qtysold[5]) AS "may_units",
    SUM(S.qtysold[6]) AS "jun_units",
    SUM(S.qtysold[7]) AS "jul_units",
    SUM(S.qtysold[8]) AS "aug_units",
    SUM(S.qtysold[9]) AS "sep_units",
    SUM(S.qtysold[10]) AS "oct_units",
    SUM(S.qtysold[11]) AS "nov_units",
    SUM(S.qtysold[12]) AS "dec_units"
FROM
    PUB.SALES AS S
WHERE
     S.company_no = '1'
GROUP BY
    S.company_no, S.prod, S.whse, S.yr
 
Marian EDU said:
don't see anything special in this SQL, beside all those cast to character and upercase calls just to form the 'record id'... I would instead get the data as is and do what ever transformation is required on the 'client' side (be it a UI or some reporting tool).

other than that try to group on columns that make up the 'record id'... and I've always though aliases are for lazy peoples like me to save some typing :)

Code: 
SELECT
    CAST(S.company_no AS VARCHAR(10)) + '!' + UCASE(CAST(S.prod AS VARCHAR(20))) + '!' + UCASE(CAST(S.whse AS VARCHAR(10))) + '!' + CAST(S.yr AS VARCHAR(4)) AS "record_id",
    SUM(S.salesamt[1]) AS "jan_sales",
    SUM(S.salesamt[2]) AS "feb_sales",
    SUM(S.salesamt[3]) AS "mar_sales",
    SUM(S.salesamt[4]) AS "apr_sales",
    SUM(S.salesamt[5]) AS "may_sales",
    SUM(S.salesamt[6]) AS "jun_sales",
    SUM(S.salesamt[7]) AS "jul_sales",
    SUM(S.salesamt[8]) AS "aug_sales",
    SUM(S.salesamt[9]) AS "sep_sales",
    SUM(S.salesamt[10]) AS "oct_sales",
    SUM(S.salesamt[11]) AS "nov_sales",
    SUM(S.salesamt[12]) AS "dec_sales",
    SUM(S.cogamt[1]) AS "jan_cogs",
    SUM(S.cogamt[2]) AS "feb_cogs",
    SUM(S.cogamt[3]) AS "mar_cogs",
    SUM(S.cogamt[4]) AS "apr_cogs",
    SUM(S.cogamt[5]) AS "may_cogs",
    SUM(S.cogamt[6]) AS "jun_cogs",
    SUM(S.cogamt[7]) AS "jul_cogs",
    SUM(S.cogamt[8]) AS "aug_cogs",
    SUM(S.cogamt[9]) AS "sep_cogs",
    SUM(S.cogamt[10]) AS "oct_cogs",
    SUM(S.cogamt[11]) AS "nov_cogs",
    SUM(S.cogamt[12]) AS "dec_cogs",
    SUM(S.qtysold[1]) AS "jan_units",
    SUM(S.qtysold[2]) AS "feb_units",
    SUM(S.qtysold[3]) AS "mar_units",
    SUM(S.qtysold[4]) AS "apr_units",
    SUM(S.qtysold[5]) AS "may_units",
    SUM(S.qtysold[6]) AS "jun_units",
    SUM(S.qtysold[7]) AS "jul_units",
    SUM(S.qtysold[8]) AS "aug_units",
    SUM(S.qtysold[9]) AS "sep_units",
    SUM(S.qtysold[10]) AS "oct_units",
    SUM(S.qtysold[11]) AS "nov_units",
    SUM(S.qtysold[12]) AS "dec_units"
FROM
    PUB.SALES AS S
WHERE
     S.company_no = '1'
GROUP BY
    S.company_no, S.prod, S.whse, S.yr
Click to expand...


Dang...I thought you may have had the golden ticket for me on that one...but even changing the GROUP BY to not use the alias didn't help. (But I learned something - thanks for that!) :)

A little background on the record_id formatting: This select statement is being dropped into a 3rd party OLAP solution that performs a nightly select against the database and builds up an aggregated table in another database (non-OpenEdge). So that's the reason the record_id field has to be formatted so explicitly.
 
I will try leaning on my provider again to see if I can get something pushed through.

In the meantime, I guess I'll start designing a set of middle-tier processes that will allow me to break up the select into smaller statements and pull them back together after they've been imported into the other database, but it's gonna be SLOOOOOOOOOOW.

But this is just a stinker and a half...because running JUST the salesamt portion of the select works...and JUST the cogsamt portion alone works...and JUST the qtysold stuff by itself works. But what's weirder is that the salesamt and qtysold work together...and the cogsamt and qtysold work together...but the salesamt and cogsamt won't work together. I'm getting a fairly generic JDBC timeout error on that one.

Meh.

Thanks for the input. I'll post an update if I get any traction with my provider. If anyone has additional input or has seen this error before, I'd welcome further input.

-M
 
Mykimus said:
But this is just a stinker and a half...because running JUST the salesamt portion of the select works...and JUST the cogsamt portion alone works...and JUST the qtysold stuff by itself works. But what's weirder is that the salesamt and qtysold work together...and the cogsamt and qtysold work together...but the salesamt and cogsamt won't work together. I'm getting a fairly generic JDBC timeout error on that one.
Click to expand...

not sure if aggregates use the same temp buffers but I would try to increase the numbers there, check the -SQLTempBuff and -Bt parameters for SQL broker start-up... looks like you hit a resource limitation of some kind.

might find some inspiration in Steve Pittman's presentations...
http://download.psdn.com/media/exch_audio/2006/DB/DB-2_pittman.ppt
http://download.psdn.com/media/exch_audio/2005/DB/DB-04_Pittman.ppt
 
Hey, Marian EDU - you nailed it!

-SQLTempBuff turned out to be the culprit! As it turns out, the default is 1MB which was EASILY being blown out by the statement I was attempting to run. We first doubled it...no good. Then doubled it again...still no good. We ended up setting it to 10MB, and it's been running great for a few weeks now. (And on top of that, the customer has mentioned that their operational system is running MUCH better and responding much faster after the change...FWIW...)

Thanks everyone for the help!!

-M
 
You must log in or register to reply here.
Back
Top