tips and tricks

Table and cloumn comments can be queried from sys.systables and sys.syscolumns system views in Sybase IQ. Comments are stored in remarks column.

In default to load data to Sybase IQ with Informatica external loader you have to transfer data to Sybase server.

With following code you can add follwing features to Informatica Sybase IQ External loader

• To be able to load data directly from Informatica Server
• Parallel External load, now instead of getting error, Informatica is waiting other transactions locking target table to finish. (Default unlimited, you can specify wait time if needed)
• Cleaning data, log and ctl files after successful load.

V1.1 updates

• External loader errors are logged file_name.err file

To configure your external loader simply point to this new file

Save following script to your Sybase IQ bin64 directory.
Download link
https://docs.google.com/file/d/0B48wKM149H9fWVFDdUZ6LUNtWWc/edit?usp=sharing

Source Code:

#!/bin/sh
#Special thanks to Jean-Philippe for initial code
print()
{
        printf "$1" >> $LOG_FILE
}
#Change this with your dbisql pathext_load_exec=/sybase/iq/IQ-15_4/bin64/dbisql
connect_str=$2
ctl_file=$4
serv_data_dir=`dirname $ctl_file`
LOG_FILE=$serv_data_dir/dbisql.log
if [ -e $LOG_FILE ]
then
                rm $LOG_FILE
fi
print "ext_load_exec=$ext_load_exec\n"
print "connect_str=$connect_str\n"
print "ctl_file=$ctl_file\n"
print "serv_data_dir=$serv_data_dir\n"

#replacing from with client file to avoid copying data to IQ server
cp $ctl_file ${ctl_file}.bak
cat ${ctl_file}.bak | sed 's/FROM /USING CLIENT FILE /g' > $ctl_file

#Getting data file name to delete after successful  load
ctl_str=`echo $ctl_file`
num=`echo ${#ctl_str}`
let "num-=4"
del_file=`expr substr $ctl_str 1 $num`
#adding lock wait for parallelisimcp $ctl_file ${ctl_file}.bak

#grepping file name
load_str=`egrep "LOAD TABLE" $ctl_file`
num=`echo ${#load_str}`
let "num-=11"
target_table=`expr substr "$load_str" 11 $num`

#adding lock wait
sed -e "10s/$/ \\
lock table  $target_table in write mode wait;/" ${ctl_file}.bak > ${ctl_file}
#adding commit to release table
echo ";commit;" >> ${ctl_file}
print "$ext_load_exec -c \"$connect_str\" $ctl_file\n"
LOG_FILE2=${ctl_file}\.err
$ext_load_exec -nogui -c "$connect_str" -q $ctl_file > $LOG_FILE2 2>&1

res=`ls -ltr $LOG_FILE2 |awk '{print $5}'`

res=$?
if [ $res -eq 0 ]
then
print "result=$res\n"
rm -f ${del_file}*;
exit $res
fi

It'is realy unique and easy to substract dates in Oracle.

If you select

SELECT TO_DATE ('01/02/2013 11:07:16','DD/MM/YYYY HH24:MI:SS') - TO_DATE('01/02/2013 11:03:37','DD/MM/YYYY HH24:MI:SS') FROM DUAL;

Result is:
0,00253472222222222


To achive same result in Sybase IQ correct syntax is:
SELECT CONVERT(NUMERIC(16,10), DATEDIFF(SECOND,  CONVERT(TIMESTAMP, '2013-02-01 11:03:37'), CONVERT(TIMESTAMP, '2013-02-01 11:07:16'))) / 60 / 60 / 24


DATEDIFF: Datepart, Small date, Big date returns: INT

Tric is converting datediff result to numeric :)

One of the best ways to send HTML mail from SybaseIQ on IBM AIX is to use sendmail.
Unfortunately sendmail has linelength limit (2040 characters default). After 2040th character sendmail is adding ! and new line characters to text which is ruining HTML.

To fix this problem:

1. Change Linelimit info in Sendmail.cf file. Add L=4096 (or any other suitable value) to corresponding mailer.
2. To identify correct mailer check mail log at /var/log/maillog file. Mailer name is mostly Mrelay
3. After these steps ! character shall be removed from your mail.
4. You may add ||  CHAR(13) || CHAR(10) after each </tr> statement to add new line in SQL as well.

Sample script which sends HTML mail from Sybase IQ is below

To split delimiter separated values in to rows use sa_split_list function

Usage:

SELECT * FROM sa_split_list( 'Tee Shirt,Baseball Cap,Visor,Shorts' );

"words exceeding the maximum permitted word length not supported" error means you did not defined enough/correct delimiters for WD index.

Maximum word length for WD index is 255 characters. You need to delimit your text with correct delimiters to reduce maximum word length under 255.

For columns, which holds SQL statements, delimited by ' ,;=' clause seems valid.This statement means string will be delimited by any of those characters. '.' character is willingly ommited to be able to find 'owner.tablename' format faster.


You may check Sybase Infocenter for detailed information.

When you compile any procedure with '\' in Sybase IQ, your code will be replaced with '\\' to avoid whis
use CHAR(92) instead.

CREATE PROCEDURE MY_TEST
BEGIN
        SELECT LIST(TABLE_NAME, CHAR(92)) FROM SYSTABLES;
END;
/

Oracle's LTRIM/RTRIM supports second parameter to remove other characters than blank.
For example
select LTRIM('My Textaaaa', 'a') from dual; will return 'My Text'.
Same behavior can be simulated with Sybase IQ as well

LTRIM:
substr(<STRING>, length(<STRING>)-length(ltrim(replace( <STRING> ,<CHARACTERS TO REMOVE>,' ')))+1)

RTRIM:
substr( <STRING> ,1,length(rtrim(replace( <STRING> , <CHARACTERS TO REMOVE> ,' '))))

Sybase IQ equavalent for Oracle wm_concat and SQL group_concat is LIST() function.

This function takes 2 parameters column_name (mandotory) and separator (optional default ",").

Sample query:
select list(table_name) from systable

Click for related Sybase Central entry

Oracle translate function has many great usages like Eliminating Double Quotes, Encryption/Decryption etc.

I just wrote SybaseIQ version of it. Code can easly be improved but version 1.0 is not a bad place to start.
Source code is below please feel free to use and comment.


CREATE FUNCTION TRANSLATE (
-- -------------------------------------------------------------------------------------
-- Designer    : Ongun Demirler
-- Author    : Ongun Demirler  
-- Description  :    Sybase IQ replacement for Oracle Translate function
--                    Overrides
--
-- Ammedments   :
--    When        Who                    What
--    ===========    ========            =========================================
--    03.04.2012    Ongun DEMIRLER        Initial design and implementation
--
--    Parameters:
--                P_STR: original string
--                P_SRC: source characters
--                P_DEST: destination characters
--  
-- -------------------------------------------------------------------------------------
    P_STR VARCHAR(4000),
    P_SRC VARCHAR(4000),
    P_DEST VARCHAR(4000) )
RETURNS VARCHAR(4000)
BEGIN
 
    DECLARE a_ret_str varchar(4000); --Return string
    DECLARE a_pointer integer; --Character pointer
    DECLARE a_src_length integer; --Length of source string
 
    set a_src_length = LENGTH(P_SRC);
    --No characters to translate from, source or to
    IF LENGTH(P_STR) = 0 OR a_src_length = 0 OR LENGTH(P_DEST) = 0 THEN RETURN ('') END IF;
 
    --Create replace statement for each character in p_src
    SET a_pointer = 1;
    SET a_ret_str = P_STR;
 
    --Replace each character from src with corresponding character from dest
    WHILE (a_pointer <= a_src_length) LOOP
        SET a_ret_str = replace(a_ret_str, SUBSTR(P_SRC, a_pointer, 1) , SUBSTR(P_DEST, a_pointer, 1));
        SET a_pointer = a_pointer + 1;
    END LOOP;
 
    RETURN (a_ret_str);
END