cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

MaxDB: Table with many LONG fields does not allow an INSERT: ...?

Go to solution
Former Member

on ‎05-28-2008 3:38 PM

0 Kudos

Hi,

I have a table with many LONG fields (28). So far, everythings works fine.

However, if I add another LONG field I cannot insert a dataset anymore

(29 LONG fields).

Does there exist a MaxDB parameter or anything else I can change to make inserts possible again?

Thanks in advance

Michael

appendix:

- Create and Insert command and error message

- MaxDB version and its parameters

-

Create and Insert command and error message

-


CREATE TABLE "DBA"."AZ_Z_TEST02"
(
	"ZTB_ID"               Integer    NOT NULL,
	"ZTB_NAMEOFREPORT"           Char (400) ASCII DEFAULT '',
	"ZTB_LONG_COMMENT"                LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_00"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_01"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_02"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_03"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_04"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_05"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_06"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_07"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_08"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_09"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_10"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_11"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_12"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_13"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_14"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_15"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_16"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_17"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_18"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_19"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_20"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_21"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_22"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_23"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_24"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_25"         LONG ASCII DEFAULT '',
	"ZTB_LONG_TEXTBLOCK_26"         LONG ASCII DEFAULT '',
	PRIMARY KEY ("ZTB_ID")
)

The insert command

INSERT INTO AZ_Z_TEST02 SET ztb_id = 87

works fine. If I add the LONG field

"ZTB_LONG_TEXTBLOCK_27"         LONG ASCII DEFAULT '',

the following error occurs:

    Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
    General error;-7032 POS(1) SQL statement not allowed for column of data type LONG
    INSERT INTO AZ_Z_TEST02 SET ztb_id = 88

-

MaxDB version and its parameters

-

All db params given by

dbmcli -d myDB -u dbm,dbm param_directgetall > maxdb_params.txt

are

KERNELVERSION KERNEL 7.5.0 BUILD 026-123-094-430

INSTANCE_TYPE OLTP

MCOD NO

RESTART_SHUTDOWN MANUAL

SERVERDBFOR_SAP YES

_UNICODE NO

DEFAULT_CODE ASCII

DATE_TIME_FORMAT INTERNAL

CONTROLUSERID DBM

CONTROLPASSWORD

MAXLOGVOLUMES 10

MAXDATAVOLUMES 11

LOG_VOLUME_NAME_001 LOG_001

LOG_VOLUME_TYPE_001 F

LOG_VOLUME_SIZE_001 64000

DATA_VOLUME_NAME_0001 DAT_0001

DATA_VOLUME_TYPE_0001 F

DATA_VOLUME_SIZE_0001 64000

DATA_VOLUME_MODE_0001 NORMAL

DATA_VOLUME_GROUPS 1

LOG_BACKUP_TO_PIPE NO

MAXBACKUPDEVS 2

BACKUP_BLOCK_CNT 8

LOG_MIRRORED NO

MAXVOLUMES 22

MULTIO_BLOCK_CNT 4

DELAYLOGWRITER 0

LOG_IO_QUEUE 50

RESTARTTIME 600

MAXCPU 1

MAXUSERTASKS 50

TRANSRGNS 8

TABRGNS 8

OMSREGIONS 0

OMSRGNS 25

OMS_HEAP_LIMIT 0

OMS_HEAP_COUNT 1

OMS_HEAP_BLOCKSIZE 10000

OMS_HEAP_THRESHOLD 100

OMS_VERS_THRESHOLD 2097152

HEAP_CHECK_LEVEL 0

ROWRGNS 8

MINSERVER_DESC 16

MAXSERVERTASKS 20

_MAXTRANS 288

MAXLOCKS 2880

LOCKSUPPLY_BLOCK 100

DEADLOCK_DETECTION 4

SESSION_TIMEOUT 900

OMS_STREAM_TIMEOUT 30

REQUEST_TIMEOUT 5000

USEASYNC_IO YES

IOPROCSPER_DEV 1

IOPROCSFOR_PRIO 1

USEIOPROCS_ONLY NO

IOPROCSSWITCH 2

LRU_FOR_SCAN NO

PAGESIZE 8192

PACKETSIZE 36864

MINREPLYSIZE 4096

MBLOCKDATA_SIZE 32768

MBLOCKQUAL_SIZE 16384

MBLOCKSTACK_SIZE 16384

MBLOCKSTRAT_SIZE 8192

WORKSTACKSIZE 16384

WORKDATASIZE 8192

CATCACHE_MINSIZE 262144

CAT_CACHE_SUPPLY 1632

INIT_ALLOCATORSIZE 229376

ALLOW_MULTIPLE_SERVERTASK_UKTS NO

TASKCLUSTER01 tw;al;ut;2000sv,100bup;10ev,10gc;

TASKCLUSTER02 ti,100dw;30000us;

TASKCLUSTER03 compress

MPRGN_QUEUE YES

MPRGN_DIRTY_READ NO

MPRGN_BUSY_WAIT NO

MPDISP_LOOPS 1

MPDISP_PRIO NO

XP_MP_RGN_LOOP 0

MP_RGN_LOOP 0

MPRGN_PRIO NO

MAXRGN_REQUEST 300

PRIOBASE_U2U 100

PRIOBASE_IOC 80

PRIOBASE_RAV 80

PRIOBASE_REX 40

PRIOBASE_COM 10

PRIOFACTOR 80

DELAYCOMMIT NO

SVP1_CONV_FLUSH NO

MAXGARBAGECOLL 0

MAXTASKSTACK 1024

MAX_SERVERTASK_STACK 100

MAX_SPECIALTASK_STACK 100

DWIO_AREA_SIZE 50

DWIO_AREA_FLUSH 50

FBM_VOLUME_COMPRESSION 50

FBM_VOLUME_BALANCE 10

FBMLOW_IO_RATE 10

CACHE_SIZE 10000

DWLRU_TAIL_FLUSH 25

XP_DATA_CACHE_RGNS 0

DATACACHE_RGNS 8

XP_CONVERTER_REGIONS 0

CONVERTER_REGIONS 8

XP_MAXPAGER 0

MAXPAGER 11

SEQUENCE_CACHE 1

IDXFILELIST_SIZE 2048

SERVERDESC_CACHE 73

SERVERCMD_CACHE 21

VOLUMENO_BIT_COUNT 8

OPTIM_MAX_MERGE 500

OPTIM_INV_ONLY YES

OPTIM_CACHE NO

OPTIM_JOIN_FETCH 0

JOIN_SEARCH_LEVEL 0

JOIN_MAXTAB_LEVEL4 16

JOIN_MAXTAB_LEVEL9 5

READAHEADBLOBS 25

RUNDIRECTORY E:\_mp\u_v_dbs\EVERW_C5

_KERNELDIAGFILE knldiag

KERNELDIAGSIZE 800

_EVENTFILE knldiag.evt

_EVENTSIZE 0

_MAXEVENTTASKS 1

_MAXEVENTS 100

_KERNELTRACEFILE knltrace

TRACE_PAGES_TI 2

TRACE_PAGES_GC 0

TRACE_PAGES_LW 5

TRACE_PAGES_PG 3

TRACE_PAGES_US 10

TRACE_PAGES_UT 5

TRACE_PAGES_SV 5

TRACE_PAGES_EV 2

TRACE_PAGES_BUP 0

KERNELTRACESIZE 648

EXTERNAL_DUMP_REQUEST NO

AKDUMP_ALLOWED YES

_KERNELDUMPFILE knldump

_RTEDUMPFILE rtedump

UTILITYPROTFILE dbm.utl

UTILITY_PROTSIZE 100

BACKUPHISTFILE dbm.knl

BACKUPMED_DEF dbm.mdf

MAXMESSAGE_FILES 0

EVENTALIVE_CYCLE 0

_SHAREDDYNDATA 10280

_SHAREDDYNPOOL 3607

USE_MEM_ENHANCE NO

MEM_ENHANCE_LIMIT 0

__PARAM_CHANGED___ 0

__PARAM_VERIFIED__ 2008-05-13 13:47:17

DIAG_HISTORY_NUM 2

DIAG_HISTORY_PATH E:\_mp\u_v_dbs\EVERW_C5\DIAGHISTORY

DIAGSEM 1

SHOW_MAX_STACK_USE NO

LOG_SEGMENT_SIZE 21333

SUPPRESS_CORE YES

FORMATTING_MODE PARALLEL

FORMAT_DATAVOLUME YES

HIRES_TIMER_TYPE CPU

LOAD_BALANCING_CHK 0

LOAD_BALANCING_DIF 10

LOAD_BALANCING_EQ 5

HS_STORAGE_DLL libhsscopy

HS_SYNC_INTERVAL 50

USE_OPEN_DIRECT NO

SYMBOL_DEMANGLING NO

EXPAND_COM_TRACE NO

OPTIMIZE_OPERATOR_JOIN_COSTFUNC YES

OPTIMIZE_JOIN_PARALLEL_SERVERS 0

OPTIMIZE_JOIN_OPERATOR_SORT YES

OPTIMIZE_JOIN_OUTER YES

JOIN_OPERATOR_IMPLEMENTATION IMPROVED

JOIN_TABLEBUFFER 128

OPTIMIZE_FETCH_REVERSE YES

SET_VOLUME_LOCK YES

SHAREDSQL NO

SHAREDSQL_EXPECTEDSTATEMENTCOUNT 1500

SHAREDSQL_COMMANDCACHESIZE 32768

MEMORY_ALLOCATION_LIMIT 0

USE_SYSTEM_PAGE_CACHE YES

USE_COROUTINES YES

MIN_RETENTION_TIME 60

MAX_RETENTION_TIME 480

MAX_SINGLE_HASHTABLE_SIZE 512

MAX_HASHTABLE_MEMORY 5120

HASHED_RESULTSET NO

HASHED_RESULTSET_CACHESIZE 262144

AUTO_RECREATE_BAD_INDEXES NO

LOCAL_REDO_LOG_BUFFER_SIZE 0

FORBID_LOAD_BALANCING NO

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
‎05-29-2008 10:07 AM
0 Kudos

Hi Michael,

this really looks like one of those "Find-the-5-errors-in-the-picture" riddles to me.

Really.

Ok, first to your question: this seems to be a bug - I could reproduce it with my 7.5. Build 48.

Anyhow, when I use


insert into "AZ_Z_TEST02"  values (87,'','','','','','','','','','','','','','','',''
                                          ,'','','','','','','','','','','','','','','','')

it works fine.

Since explicitely specifying all values for an insert is a good idea anyhow (you can see directly, what value the new tupel will have), you may want to change your code to this.

Now to the other errors:

- 28 Long values per row?

What the heck is wrong with the data design here?

Honestly, you can save data up to 2 GB in a BLOB/CLOB.

Currently, your data design allows 56 GB per row.

Moreover 26 of those columns seems to belong together originally - why do you split them up at all?

- The "ZTB_NAMEOFREPORT" looks like something the users see -

still there is no unique constraint preventing that you get 10000 of reports with the same name...

- MaxDB 7.5 Build 26 ?? Where have you been the last years?

Really - download the 7.6.03 Version [here|https://www.sdn.sap.com/irj/sdn/maxdb-downloads] from SDN and upgrade.

With 7.6. I was not able to reproduce your issue at all.

- Are you really putting your data into the DBA schema? Don't do that, ever.

DBM/SUPERDBA (the sysdba-schemas) are reserved for the MaxDB system tables.

Create a user/schema for your application data and put your tables into that.

KR Lars

Show replies
Show replies
Former Member
‎05-29-2008 11:45 AM
0 Kudos

> 

> Hi Michael,
> 
> this really looks like one of those "Find-the-5-errors-in-the-picture" riddles to me.
> Really.
> 
> Ok, first to your question: this seems to be a bug - I could reproduce it with my 7.5. Build 48.
> Anyhow, when I use
> 

> insert into "AZ_Z_TEST02"  values (87,'','','','','','','','','','','','','','','',''
>                                           ,'','','','','','','','','','','','','','','','')
> 
> it works fine.

It solves my problem. Thanks a lot. -- I hardly can believe that this is all needed to solve the bug. This may be the reason why I have not given it a try.

> 

Since explicitely specifying all values for an insert is a good idea anyhow (you can see directly, what value the new tupel will have), you may want to change your code to this.
> 
> Now to the other errors:
> - 28 Long values per row? 
> What the heck is wrong with the data design here? 
> Honestly, you can save data up to 2 GB in a BLOB/CLOB. 
> Currently, your data design allows 56 GB per row. 
> Moreover 26 of those columns seems to belong together originally - why do you split them up at all?
> 
> - The "ZTB_NAMEOFREPORT" looks like something the users see - 
> still there is no unique constraint preventing that you get 10000 of reports with the same name...

You are right. This table looks a bit strange. The story behind it is: Each crystal report in the application has a few textblocks which are the same for all the e.g. persons the e.g. letter is created for. Principally, the textblocks could be directy added to the crystal report. However, as it is often the case, these textblocks may change once in a while. Thus, I put the texts of the textblock into this "strange" db table (one row for each report, one field for each textblock, the name of the report is given by "ztb_nameofreport"). And the application offers a menue by which these textblocks can be changed. Of course, the fields in the table could be of type CHAR, but LONG has the advantage that I do not have to think about the length of the field, since sometime the texts are short and sometimes they are really long.

(These texts would blow up the sql select command of the crystal report very much if they were integrated into the this select command. Thus it is realized in another way: the texts are read before the crystal report is loaded, then the texts are "given" to the crystal report (by its parameters), and finally the crystal report is loaded.)

> 

 - MaxDB 7.5 Build 26 ?? Where have you been the last years? 
> Really - download the 7.6.03 Version [here|https://www.sdn.sap.com/irj/sdn/maxdb-downloads] from SDN and upgrade. 
> With 7.6. I was not able to reproduce your issue at all.

The customer still has Win98 clients. MaxDB odbc driver 7.5.00.26 does not work for them. I got the hint to use odbc driver 7.3 (see [lists.mysql.com/maxdb/25667|lists.mysql.com/maxdb/25667]). Do MaxDB 7.6 and odbc driver 7.3 work together?

All Win98 clients may be replaced by WinXP clients in the near future. Then, an upgrade may be reasonable.

> 

 - Are you really putting your data into the DBA schema? Don't do that, ever.
> DBM/SUPERDBA (the sysdba-schemas) are reserved for the MaxDB system tables.
> Create a user/schema for your application data and put your tables into that.
> 
> KR Lars

In the first MaxDB version I used, schemas were not available. I haven't changed it afterwards. Is there an easy way to "move an existing table into a new schema"?

Michael

lbreddemann
Active Contributor
‎05-29-2008 12:29 PM
0 Kudos

Hi Michael,

thanks for explaing the data design.

Anyhow - for that requirement I would have proposed to use another design.

1. Option (one table - multiple rows design)

ID (INTEGER) - REPORTNAME (VARCHAR(400)) - TEXTBLOCK_NUMBER (Integer) - TEXTBLOCK (BLOB)

That way you'll have one row for each TEXTBLOCK per Report in the table. The tabledesign is kept small and the client application only needs to reserve memory for one BLOB at a time.

Also you can create an index on REPORTNAME and TEXTBLOCK_NUMBER so that you can easily access the right data.

2. Option (two tables - single rows)

Table REPORTS

ID - REPORTNAME

Table TEXTBLOCKS

REPORTID- TEXTBLOCK_NUMBER - TEXTBLOCK

That way you also keep the number of columns small and just get the blocks that you need.

To change the owner of the tables you can use the loadercli or the transport (Export/Import) functionality from the DB Studio.

KR Lars

Former Member
‎06-04-2008 2:48 PM
0 Kudos

> 

> - MaxDB 7.5 Build 26 ?? ...
> Really - download the 7.6.03 Version [here|https://www.sdn.sap.com/irj/sdn/maxdb-downloads] from SDN and upgrade. 
> With 7.6. I was not able to reproduce your issue at all.
> KR Lars

Hi Lars,

I cannot find an odbc driver 7.6 there.

- Can I still use the odbc driver 7.5.00.26 with MaxDB 7.6.03.15? I guess it's possible.

- Nevertheless, where can I download an odbc driver 7.6.xx.xx?

Kind regards.

Michael

lbreddemann
Active Contributor
‎06-05-2008 6:59 AM
0 Kudos

Hi Michael,

yes you can use the 7.5 ODBC driver to access a 7.6 MaxDB.

For the 7.6 driver there is no seperate download - it's included in the package available here in SDN.

You can use SDBSETUP/SDBINST to select what components should be installed.

KR Lars

Answers (2)

Answers (2)

Former Member
‎06-12-2008 11:46 AM
0 Kudos

Hi,

when handling LONG-defaults, for each default a number of bytes will be reserved in a buffer together with some info. If you have many LONG-defaults to be handled, some KB are summed up. In 7.5 this number is greater than the maximum size in case of more than 28 LONG-columns.

In version 7.6 (don't ask for the exact release), the maximum number of bytes available was increased. Therefore in 7.6 your problem was not reproduceable.

As it is a restriction in the kernel, upgrading the ODBC-driver will not help. An upgrade of the kernel would be necessary.

If that is not possible because of customer-restriction, then the number of LONG-columns has to be restricted or this VALUES (' ', ' ', ' ', ..... ) has to be used. But this overwrites the default-clause, thus making it superfluous.

Just to bring some further light to this topic...

Elke

Show replies
Show replies
Former Member
‎06-12-2008 1:15 PM
0 Kudos

thanks a lot for your infos.

Michael

TTK
Employee
Employee
‎05-29-2008 4:19 PM
0 Kudos

Hello Michael

Regarding Win98 you could try the ODBC drivers of 7.3 or 7.4.3. AFAIK, you can still get them from [www.sapdb.org].

Regards Thomas

Show replies
Show replies
Former Member
‎05-29-2008 5:32 PM
0 Kudos

Hi Thomas,

> 

> Regarding Win98 you could try the ODBC drivers of 7.3 or 7.4.3.

Do you mean that MaxDB 7.6.03.15 "works together" with odbc driver 7.3? and also with 7.4.3?

> 

> AFAIK, you can still get them from [www.sapdb.org].

Thanks.

By the way, Is there still a download page for for MaxDB 7.5 available?

[www.sdn.sap.com/irj/sdn/maxdb-downloads|http://www.sdn.sap.com/irj/sdn/maxdb-downloads] only talks about 7.6. (Meanwhile, the former page [www.mysql.com/sap/|http://www.mysql.com/sap/] points to [www.sdn.sap.com/irj/sdn/maxdb|https://www.sdn.sap.com/irj/sdn/maxdb].)

Kind regards,

Michael

Ask a Question