SAP Knowledge Base Article - Preview

2402258 - sp_archive syntax error - SYBASE Aleri Streaming Platform

Symptom

  • IQ has been upgraded from version 15.4 to 16.
     
  • Aleri Streaming Platform's sp_archive utility is being used to load data into IQ using the following syntax:

/aleri/platform/3.2.1/bin/sp_archive -f /xyz/sp_archive_config.xml -p myhost:myport -c user:pass -P 16 -D -W 60000 -r 3 -T 120

  • The LOAD TABLE statement used by sp_archive includes the IQ BLOCK SIZE parameter.
     
  • However, the IQ BLOCK SIZE option was deprecated in IQ 15.2, and was discontinued in IQ 16.0 and above.  Therefore, using the BLOCK SIZE option with IQ 16 now causes the sp_archive process to fail.
     
  • In order to prevent this failure, the BLOCK SIZE option has been commented out of the LOAD TABLE statement contained in the sp_archive's configuration xml file:

sp_archive_config.xml:

<PlatformArchive
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:noNamespaceSchemaLocation="file:///aleri/platform/3.2.1/etc/sp_archive.xsd" >

  <SybaseIQ id="..." dsn="...">
        <Option name="FORMAT" value="BINARY"/>
        <Option name="ESCAPES" value="ON"/>
        <!--Option name="BLOCK SIZE" value="1000000"/ -->
        <Option name="IGNORE CONSTRAINT" value="UNIQUE 0 MESSAGE LOG '/log/m.log' ROW LOG '/log/r.log' "/>
        <Option name="ONLY LOG" value="UNIQUE, NULL, DATA VALUE LOG DELIMITED BY ','"/>
  </SybaseIQ>

  <Streams id="...">
    <Stream sourceName="..._log" targetName="..." controlStreamName="..._truncate" logStreamName="..._log"/>
  </Streams>

  <Archive target="MyTable" archiveStreams="..." tempDir="/xyz" logDir="/xyz"/>

</PlatformArchive>

  •  Even though the BLOCK SIZE option has been commented out, the sp_archive still fails with the following error when using Aleri with IQ 16:

load table MyTable(col1,col2,...) from '/myfile.csv'  BLOCK SIZE 100000 DELIMITED BY '' ESCAPES OFF FORMAT ASCII IGNORE CONSTRAINT UNIQUE 0 MESSAGE LOG '/log/m.log' ROW LOG '/log/r.log'  ONLY LOG UNIQUE, NULL, DATA VALUE LOG DELIMITED BY ',' QUOTES OFF ROW DELIMITED BY '
'; commit;
SqlState : 42000 Error Message :[Sybase][ODBC Driver][Sybase IQ]Syntax error near 'BLOCK' on line 1
ERROR: While bulk loading data for Stream to exernal repository.
ABORTING ARCHIVE.

DATA ARCHIVAL FAILED TO FINISH SUCCESSFULLY ON: <date>


Read more...

Environment

  • Sybase Aleri Streaming Platform 3.2.1
  • Sybase IQ 15.4.0.3027/130221/P/ESD 3/Sun_x64/OS 5.10/64bit/2013-02-21 19:58:48
  • SAP IQ 16.0.110.2304/10339/P/sp11.04/Sun_x64/OS 5.10/64bit/2016-10-07 03:12:14

Product

Sybase Aleri Streaming Platform 3.1

Keywords

KBA , BC-SYB-CEP , Sybase Aleri Streaming Platform (CEP) , Problem

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.