MaxDb 7.6 Output Size

[Former Member]

What is the limit on the size of the output of a SQL query; and is this

configurable.

[Former Member]

My answers to your questions:

a) which exact MaxDB version you are using (incl. Build-nr.)

MaxDB 7.6.00.37

b) the table definition

LineItem and LineItemOvfl table each table has 512 columns and they are being joined together

using a simple inner join on the primary key Id and number of columns that are being generated from the join are approximately

1000 columns although alot of them have null values.

c) UNICODE ?

No

[Former Member]

I tried your suggestion and found the "_PACKETSIZE" parameter to already be at the maximum length which is 131072. When I perform the query I still get the same error below

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed

General error;-2003 POS(8) Output columns too long

select * from LineItem inner join LineItemTaxOvrflw on LineItem.lineItemId=LineItemTaxOvrflw.lineItemId

The tables that I am joining don't fail on other databases such as Oracle,Sql Server,MYSQL, and DB2.

[roland_mallmann]

Hey David,

the official documentation states the following on your -2003 error:

"

Explanation:

The following reasons are possible:

1. The total length of the output columns exceeds 8084 bytes.

2. The total length of the output columns, together with the columns specified in the ORDER or GROUP clause, exceeds 8084 bytes.

3. The total length of the output columns, together with the correlated columns and columns of predicates that contain a subquery, exceeds 4048 bytes in an SQL statement with correlated subqueries.

User Action:

1. Reduce the number of output columns.

2. Try to do without sorting or grouping, or to perform sorting or grouping across shorter or fewer columns.

3. Try to manage with fewer output columns or correlated columns, or to achieve the desired effect using several SQL statements

"

---

Can you maybe inform us about:

a) which exact MaxDB version you are using (incl. Build-nr.)

b) the table definition(s)

c) UNICODE ?

Kind regards,

Roland

[Former Member]

The issue is the total length of the output columns exceeds 8084 bytes.

The recommended user actions are not condusive to porting to Maxdb for a jdbc application that supports multiple databases.

1. Reduce the number of output columns.

2. Try to do without sorting or grouping, or to perform sorting or grouping across shorter or fewer columns.

3. Try to manage with fewer output columns or correlated columns, or to achieve the desired effect using several SQL statements.

If there any plan to increase the MaxDB limit or spread the "row output" across more than 1 block?

[Former Member]

Does anyone know the limit in 7.7?

[Former Member]

The client is a java program and it uses a JDBC driver in order to do database access. When I do a select using a JDBC driver what is the maximum length

of the data that can be returned from a table assuming a single row, and is this a configurable option.

[Former Member]

There is the parameter PACKETSIZE that defines the maximum size of a communication packet sent by the database. The maximum length of the statement directly depends on it, but there's no easy formula that directly derives from the PACKETSIZE the maximum length of an SQL statement or the output of the command.

The lower and upper limits are 16384 <= PACKETSIZE <= 131072

PACKETSIZE limits the length of the communication packets that transfer SQL statements and data. Increasing the PACKETSIZE accelerates data transfer for bulk commands, and also enlarges the storage requirements per user task.

As a rule of thumb you can take the parameter PACKETSIZE, and subtract the parameter MINREPLYSIZE and ~ 1 KB (overestimation of the protocol overhead) to come up with the maximum output size.

[Former Member]

Hi David,

this depends on the client you are using. How are you querying the data? Do you mean the # of rows returned, or the data size of the returned query?

In R/3, you can specify the # of result rows to be returned, while in SQL studio, the entire result set is returned.

Thanks,

Ashwath