Migration from Oracle

4 posts / 0 new
Last post
lampe
lampe's picture
Offline
Last seen: 2 years 11 months ago
Joined: Oct 13 2011
Junior Boarder

Posts: 2

Leandro Lampe
Migration from Oracle

Guys,

I am trying to migrate one Orace BI Database to InfiniDB in order to check the performance improvement and then to upgrade my production environment, but I couldn't create some tables from Oracle because It have a lot of columns and I receive follow errors. Is there any solution ?

mysql> CREATE TABLE DIM_CAT_DEVICE
-> DEVICE_TYPE VARCHAR(2000),
DISTRIBUTED VARCHAR(2000),
( ID BIGINT,
-> SITE_NAME VARCHAR(2000),
-> DEVICE_TYPE VARCHAR(2000),
-> DISTRIBUTED VARCHAR(2000),
-> DEVICE_MODEL_NUMBER VARCHAR(2000),
-> DEVICE_PAIRING VARCHAR(2000),
IP_ADDRESS VARCHAR(2000),
SUBNET_MASK VARCHAR(2000),
SOFTWARE_VERSION VARCHAR(2000),
-> HOST_NAME VARCHAR(2000),
-> DEVICE_PAIRING VARCHAR(2000),
-> IP_ADDRESS VARCHAR(2000),
-> SUBNET_MASK VARCHAR(2000),
-> PE_IP_ADDRESS VARCHAR(2000),
-> LOGIN_CREDENTIALS_USER_ID VARCHAR(2000),
-> Login_Credentials_Password VARCHAR(2000),
-> TELNET_SSH1_OR_2 VARCHAR(2000),
-> SNMPV3_USER VARCHAR(2000),
-> SNMPV3_CONTEXT VARCHAR(2000),
-> MAINT_CONTACT_INFO VARCHAR(2000),
SNMPV3_PROTOCOL VARCHAR(2000),
-> SNMPV3_AUTHENTICATION_PASSWORD VARCHAR(2000),
-> SNMPV3_PRIVACY_PASSWORD VARCHAR(2000),
-> CUSTOMER_END_CUSTOMER VARCHAR(2000),
SERVICE VARCHAR(2000),
COMMUNITY_STRINGS_RO VARCHAR(2000),
-> COMMUNITY_STRINGS_RW VARCHAR(2000),
-> CPU_CRITICAL VARCHAR(2000),
SMARTNET_CONTRACT_NUMBER VARCHAR(2000),
-> MEMORY_MAJOR VARCHAR(2000),
MEMORY_CRITICAL VARCHAR(2000),
BUFFERS_MAJOR VARCHAR(2000),
MAINT_CONTACT_INFO VARCHAR(2000),
-> BUFFERS_CRITICAL VARCHAR(2000)
) engine=infiniDB CHARACTER SET UTF8; MAINT_VENDOR VARCHAR(2000),
-> NETFLOW_ENABLED_Y_N VARCHAR(2000),
-> DEVICE_ROLE VARCHAR(2000),
-> CUSTOMER_END_CUSTOMER VARCHAR(2000),
-> SERVICE VARCHAR(2000),
-> IV_ZONE VARCHAR(2000),
-> CPU_MAJOR VARCHAR(2000),
-> CPU_CRITICAL VARCHAR(2000),
-> MEMORY_MAJOR VARCHAR(2000),
-> MEMORY_CRITICAL VARCHAR(2000),
-> BUFFERS_MAJOR VARCHAR(2000),
-> BUFFERS_CRITICAL VARCHAR(2000)
-> ) engine=infiniDB CHARACTER SET UTF8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns

bdempsey
bdempsey's picture
Offline
Last seen: 2 months 15 hours ago
Joined: Oct 27 2009
Platinum Boarder

Posts: 194

Robert Dempsey
Re: Migration from Oracle

As the message says, you cannot create a table in InfiniDB (or MySQL for that matter) that has a row width greater than 64K bytes. You will have to trim down those varchar(2000)'s to something smaller.

lampe
lampe's picture
Offline
Last seen: 2 years 11 months ago
Joined: Oct 13 2011
Junior Boarder

Posts: 2

Leandro Lampe
Re: Migration from Oracle

I can't trim these columns, because I need put 2000 characters. In Oracle I can use this size. Isn't there solution to implement this on InfiniDB ?

bdempsey
bdempsey's picture
Offline
Last seen: 2 months 15 hours ago
Joined: Oct 27 2009
Platinum Boarder

Posts: 194

Robert Dempsey
Re: Migration from Oracle

You can break the table up into multiple tables, but each table row must be less than 64k bytes.

In any event, if all your data items are indeed 2000 bytes long, you will not get very good performance from InfiniDB, either.

In order to take useful advantage of InfiniDB's distributed join and extent elimination architecture you need make your join columns 64-bit (or less) data types (e.g. BIGINT, INT, DATE, etc.).