Title: Arial 28pt.

1
BID203 Transitioning from ASE to IQ for
Decision Support
Steven J. Bologna Principal Consultant Bologna_at_syb
ase.com August 15-19, 2004
2
The Enterprise. Unwired.
3
The Enterprise. Unwired.
Industry and Cross Platform Solutions
Unwire People
Unwire Information
Manage Information

• Adaptive Server Enterprise
• Adaptive Server Anywhere
• Sybase IQ
• Dynamic Archive
• Dynamic ODS
• Replication Server
• OpenSwitch
• Mirror Activator
• PowerDesigner
• Connectivity Options
• EAServer
• Industry Warehouse Studio

• Unwired Accelerator
• Unwired Orchestrator
• Unwired Toolkit
• Enterprise Portal
• Real Time Data Services

• SQL Anywhere Studio
• M-Business Anywhere
• Pylon Family (Mobile Email)
• Mobile Sales
• XcelleNet Frontline Solutions
• PocketBuilder
• PowerBuilder Family
• AvantGo

Sybase Workspace
4
Outline

• Overview
• SQL Syntax
• Useful Compatible/comparable commands
• Whats different that you need to know!
• Error Handling
• Things to watch out for
• Indexing 101
• Fun tools that ASE Doesnt have!
• Utilities that you might want to build?
• Tuning
• Error Handling
• SQL Tricks
• Utilities that I built
• Questions

5
SQL Syntax

• Which SQL Dialect to choose?
• Watcom SQL
• Transact SQL (T-SQL)
• Most customers choose Watcom
• More Features
• More Error Control
• Utility programs written in Watcom
• Slightly more ANSI SQL standard
• For those who want to get up to speed quickly
• Choose T-SQL learning curve easier
• Slightly less Error control (more later)
• Can use all of IQs functionality with T-SQL

6
SQL Syntax

• Watcom/ASA has additional
• Error Handling
• Function Calls
• This processed by ASA parsing
• Flow Control
• create function dbo.waitfor_delay( in s_tm
  integer )
• begin
• declare cmd varchar(200)
• select xp_cmdshell sleep str(s_tm)
  into cmd
• execute immediate cmd
• end

7
Useful Compatible/comparable commands

• ASE IQ
• Sp_help sp_columns/sp_table
• sp__helptable
• sp_h
• Sp_configure sp_iqconfigure
• sp_iqcheckoptions
• Dbcc sqltext sp_iqcontext
• Sp_who sp_iqcontext, sa_conn_info,
• IQ Utilities
• Defncopy dbxtract (not exact)
• Sp_helptext sp_helptext owner.object
• Sp_helpindex sp__helpindex
• Kill drop connection
• Sp_sysmon IQ UTILITIES, sa_eng_properties
• Waitfor delay sleep x

8
Useful Compatible/comparable commands

• Dbcc checkdb() sp_iqcheckdb check
• Isql dbiql
• Sqsh sqsh (\set semicolon_hack1)

9
Useful Compatible/comparable System Tables

• Tables IQ
• Sysobjects sysobjects view
• SYS.SYSTABLE
• SYS.SYSINDEX
• Syscolumns SYS.SYSCOLUMN
• Sysindexes SYS.SYSINDEX
• SYS.SYSIXCOL
• Syscomments SYS.SYSPROCEDURE
• SYS.SYSTRIGGER
• Sysprocesses IQ UTILTIES
• sp_iqwho
• Sysconfigures DBA.SYSOPTIONDEFAULTS
• SYS.SYSOPTION
• Wrote my own.
• Available at IQ User-group Website

10
Whats different that you need to know!

• Think lots of rows when doing operations
• IQ works better when operating on lots of rows.
• Dont select 1 row all the time..
• General rule of thumb
• 100,000 rows per second on load/insert/update(gene
  ral minimum)
• Think Fast!!
• Dont use rowcounts with deletes!
• If you want to do this
• create temp table
• Fill temp table
• Do the delete against real table joining keys of
  temp table.

11
Whats different that you need to know!

• SQL is syntax checked at run time
• This can catch you at the wrong time.
• Syntax usually catches first row not actual row
  of syntax
• Single quotes vs. double quotes usually problem
• Error like
• Select name from sysobjects where type U
• Msg 207, Level 16, State 0
• ASA Error 143 Column U not found
  
• Set options are viewable by system tables
• Sp_iqconfigure
• Temporary options not seen
• DBA.SYSOPTIONDEFAULTS
• SYS.SYSOPTION

12
Temp tables

• Three different types
• Global temporary tables(ANSI 92 spec)
• Exist across user connections for the Login
• Persistent across Begin/end blocks.
• Located in System tables
• Must have Resource (for user), DBA (for another
  user)
• Create global temporary table gt1( col1 int not
  null)
• on commit preserve rows
• Local temporary tables(ANSI 92 spec)
• Not persistent across begin/end blocks.
• Declare local temporary table lt1
• (col1 int not null)
• On commit preserve rows
• temp tables (ASE specific)
• Not persistent across BEGIN/END blocks

13
Whats different that you need to know!

• Bcp from ASE to IQ
• Format is slightly different
• Need to append to end of list an extra delimiter
• from this
• 123
• To this
• 123
• Might want to set up Insert from location
• Alternatively set up CIS proxy tables

14
Whats different that you need to know!

• No indexes in ASE when loading to get the fastest
  performance
• IQ put all indexes on
• Indexes are loaded then merged on the fly.
• Two stages
• Selects can occur when data is loaded.
• Remember 1 writer

15
Things to watch out for

• Since 1 Writer per table can run into exclusive
  table blocks
• Happens on
• Load table
• Inserts
• Update
• Deletes
• Cause Rollbacks.
• This increases the transaction time Wastes
  valuable resources.
• Better to check or to acquire dummy lock on the
  table.
• Created function
• Check_locks(owner.table)
• Similar to sp_iqlocks looking for E W

16
Indexing 101

• The basics
• Create table syntax
• Create table (column1 int not null)
• Create index syntax
• Create index_type index
• on owner.table
• (column1, )
• Delimited by - used for Word indexes
• Index_type CMP, HG, HNG, LF, WD, DATE, TIME, DTTM

17
Indexing 101

• What to add indexes on
• All join columns
• Either LF or HG first
• Then Add Date/Datetime, Time DATE,DTTM,TIME
  indexes
• Special Columns
• Cmp for comparing 2 columns
• WD Index for google type searches
• Dont forget the PK or UNIQUE HG INDEX
• HG,LF only type of UNIQUE index

18
Indexing 101

• Remember
• Always get FP index
• IQ indexes always on individual columns
• Except PK which is on Multiple columns
• HG is only index on multiple columns
• IQ UNIQUE clause
• Create table x1( col1 int not null IQ UNIQUE 255)
• Tried several tests vs. not specifying the
  value.
• Slight performance differences (within 5)
• So do you forget the UNIQUE clause? Hmmm
• Remember can only specify this at create table
  time!!
• To reset the value you must move table/drop
  recreate with higher value
• This saves storage space by adjusting the size of
  storage.
• If you know the value set it. Try to get some
  sample data!
• Specify when unique less than 65536

19
Indexing 101

• So far..
• 50-80 of the time there is a missing index
• 10 Rewrite the SQL
• 10 help the optimizer
• 1 something Else
• Optimizer is VERY good in IQ.
• Have to give it HG or LF to know cardinality.
• More with Utility programs.
• Can create FK/Join indexes to speed the indexes
  in IQ.
• Have to update join indexes via
• Synchronize join index join_index_name

20
Indexing 101

• So
• 1 FP by default
• Join columns either HG OR LF
• Date,Date-time, TIME used in where critera add
  DATE, DTTM, TIME
• If compare in where clause then CMP
• PK create UNIQUE HG index
• HNG as needed
• Could end up with 3 or 4 indexes on join columns.

21
Fun tools that ASE Doesnt have!

• Contains (for word indexes)
• Graphical Performance Plan (See ASE 15.0 for
  comparable )
• DSS type Query Engine (NTILE, RANK, Dense_Rank,
  Variance more coming!)
• Insert from Location
• Create index on-line!
• I can Log all SQL Commands!
• Watch SQL that is running.
• Create Function
• Timer functions

22
Utilities that you might want to build?

• Monitoring of processes?
• Sp_iqcontext
• Sp_iqtransaction
• Sa_conn_info
• May want to combine to form sp_who variant
• Monitoring of space per table
• Sp_iqspaceinfo
• Wait for Locks?
• Check_locks(table_name)

23
Utilities that you might want to build?

• Monitoring of IQ indexes
• Looking for LF indexes that are over 1000-5000
  unique values going to 10,000
• Select count(distinct column) from table
• At 10,000 will get out of unique values
• Monitoring of total space consumed
• Sp_iqstatus
• SQL log scanner programs
• To look at the IQ logs and look for slow
  operations.

24
Tuning

• Set options to view query plan.
• set option sa.Query_Detail'on'
• set option sa.Query_Name'my_query_name'
• set option sa.Query_Plan'on'
• set option sa.Query_Plan_After_Run'on'
• set option sa.Query_Plan_As_HTML'on'
• set option sa.Query_Timing'on'
• How to monitor speed. OLD ASE trick. 1
• declare _at_dtm datetime
• select _at_dtm getdate()
• STATEMENT
• select datediff(ms,_at_dtm,getdate())

25
Tuning

• Old ASE trick 2
• timex isql
• Isql p Usa SIQSERVER Ppassword isql_cmd
• 25 asiqdemo..1gt select count() from sysobjects
• 25 asiqdemo..2gt go
• count()
• -----------
• 570
• (1 row affected)
• Clock Time (sec.) Total 0.001 Avg 0.001
  (1228.50 xacts per sec.)
• Look at the log file..
• Times rounded to nearest second.
• Load time every 100,000 rows
• Possibly up to 2 passes.

26
Tuning

• SQL timings a bit off in the error log.
• best to time statements
• This is with
• getdate() and datediff
• Run 2 times for stability.
• QUERY_TEMP_SPACE_LIMIT
• When hit this this means that the optimizer may
  not know enough detail.
• It is estimating lots of data and lots of rows
  resulting in Large QUERY_TEMP_SPACE_LIMIT

27
Tuning- watch log file.

• Watch the timing of individual SQL Statement
• Goes Through Phases
• Begin
• Commit
• Post commit
• Like this
• Insert
• 2004-06-30 080648 0000000002 Txn 39581
• 2004-06-30 080649 0000000002 Cmt 39582
• 2004-06-30 080649 0000000002 PostCmt

28
Tuning- watch log file.

• Update
• 2004-06-30 080925 0000000002 Txn 39583
• 2004-06-30 080925 0000000002 Update Started
• 2004-06-30 080925 0000000002 sa.x1
• 2004-06-30 080925 0000000002 20895 Update
  Pass 1 completed in 0 seconds.
• 2004-06-30 080925 0000000002 20895 Update
  Pass 2 completed in 0 seconds.
• 2004-06-30 080925 0000000002 20896 Update
  for 'sa.x1' completed in 0 second
• s. 1 rows updated.
• 2004-06-30 080926 0000000002 Cmt 39584
• 2004-06-30 080926 0000000002 PostCmt
• Note all the same Connection 0000000002
• Same format Txn
• Cmt
• PostCmt

29
Tuning- watch log file.

• DELETE
• 2004-06-30 081132 0000000002 Txn 39587
• 2004-06-30 081132 0000000002 20917 Delete of
  1 rows started for table
• 2004-06-30 081132 0000000002 20919
• Delete of 1 rows completed for table sa.x1, 0
  seconds.
• 2004-06-30 081132 0000000002 Cmt 39588
• 2004-06-30 081132 0000000002 PostCmt
• Load Table
• In table 'sa.history', the full width insert of
  15 columns will begin
• at record 1.
• 2004-04-08 001029 0000000013 Insert Started
• 2004-04-08 001029 0000000013 sa.history
• 2004-04-08 001029 0000000013 20897
  1000 Rows, 0 Seconds (this will continue)
• 2004-04-08 001030 0000000013 20895 Insert
  Pass 1 completed in 1 seconds.
• 2004-04-08 001030 0000000013 20895 Insert
  Pass 2 completed in 0 seconds.
• 2004-04-08 001030 0000000013 20834
• 7866 records were inserted into
  'sa.history'.
• 2004-04-08 001031 0000000013 20896 Insert
  for 'sa.history' completed in 2 seconds. 7866
  rows inserted.

30
Tuning - Selects

• Some of the basics
• Look for 0.400000 in the HTML Plan
• Look for 0.200000
• Look for 0.100000
• Look for 0.800000
• Look for Large Result set in the output
• IQ likes HASH based Plans
• Look for the LEAF that is running the longest.
  This may be right or Wrong But is a good first
  step.
• Look for FP index with no other index type
  available. (good for Improvement)
• Example HTML output for tuning.

31
Tuning - Selects

• Sample Plan- Lets look at some.

32
Tuning Usefulness Constant

• Usefulness of values
• constant in "in list"
• 9 1 -3
• 8 4-100
• 7 500
• 6 1014
• Example Partial Query plan
• Usefulness of values
• constant in "in list"
• 9 1 -3 Right outer join(Hash)
• 8 4 Right outer join(Hash)
• 8 5-100 outer join (sort merge)
• 7 500 Left outer Join (Hash)
• 6 1014 Left outer join (Hash)

33
Tuning Load table

• What to look for
• Rows going in
• Commit phase 1
• Commit phase 2
• Speed that I have seen on this
• Phase 1
• Rows Seconds Average
• 18713458 233 80315 rows/sec (slow side)
• 287614309 1471 195,522 rows/sec
• Phase 2
• 8713458 19 458603 rows/sec
• 287614309 758 379,438 rows/sec

34
Loading

• Might want to set up IQ to ASE CIS proxy tables
• CIS FROM IQ to ASE
• Add server to Interfaces file
• Add Server to system tables
• Add external login
• CIS from ASE to IQ
• Add Server to Interfaces file
• Add Server to sysservers
• Add External login
• Use master
• Grant connect to public or user

35
Tuning - Deletes

• HG_DELETE_METHOD
• 0, 1, 2
• 0 is default
• 1 is small method
• 2 is large method
• Personal Experience
• 1 seems to be a bit faster..(for what I was
  doing)
• 12.6 will change this

36
Error Handling

• This is a fun one!
• Better to use SQLCODE, SQLSTATE than _at__at_error
• This will be changing a bit..
• Error handling within Procedures is slightly
  different than outside
• Better to create stored procedure than
• But you DBAs out there have to be able to
  script!!
• Set options
• ON_TSQL_ERROR T-SQL
• ON_ERROR DBISQL

37
Error Handling T-SQL

• SET TEMPORARY OPTION SA.ON_TSQL_ERROR
• CONDITIONAL
• is on exception resume set?
• yes continue
• No exit procedure
• CONTINUE
• STOP
• Insert into x1(col1, col2) values (1,2,4)
• select _at_cd SQLCODE, _at_err _at__at_error,
  _at_stSQLSTATE
• NOTE
• SQL Batches operate differently than stored
  procedure (today)

38
Error Handling Watcom/ANSI

• CREATE PROCEUDRE
• ON EXCEPTION RESUME
• if SQLCODE ltgt 0 then
• return( NULL )
• end if
• Set option On errorstop or Continue

39
SQL Tricks

• Materialized Tables
• Relatively new feature
• Create select statement inside a FROM clause
• Select table_name from SYS.SYSTABLE st ,(
• Select Select name from sysobjects where type
  U) as t2
• Where st.name t2.table_name
• Create function then use it in a SQL statement
• Handy for reusing the function inside SQL code.
• Remember that ASA will probably parse this.
• So make sure runs fast before put function on.

40
SQL Tricks

• Where datepart(day,column) 23
• In ASE this would be slow!!
• IN IQ
• Put a DATE, TIME, DATETIME index on and watch the
  statement speed up!
• So date functions are allowed in IQ.
• Much better indexing methods!!
• And this is at least 100X faster!!!

41
Utilities that I built

• Sp_configure / sp_iqconfigure
• Constantly looking up the configuration values
• Wanted to know what was set for a particular user
• Sp_who /sp_w
• Sp_iqcontext was too long! Wanted short output

42
Utilities that I built

• New version of sp_help / sp_h
• Needed to. See DDL and not FP indexes
• Example of output
• New version is in the works. shorter output
• Sample Output
• column_name data_type
  scale width NULL cardinality
• ------------------------------ ---------------
  ------ ----------- ----
  ---------------
• c1 int
  0 4 0 3
• c2 int 0 4 1 0
• (2 rows affected)
• TYPE INDEX_NAME
  ordered_list
• ---- -----------------------------------------
  --------------------------------
• HG x1_HG_c1
  c1

43
Utilities/Manuals that are out there!

• http//www.odscompanies.com/iqug/iqug.html
• Web site with good utilities and some quick info
• http//www.sypron.nl/asiq_qref.html
• IQ quick reference guide
• Other IQ quick information
• http//sybooks.sybase.com/onlinebooks/group-iq/iqg
  1250e
• IQ online manuals
• Code Exchange
• Code Exchange for IQ

44
Questions?

• You can reach me at
• 1000 town Center
• Suite 1800
• Southfield MI 48075
• bologna_at_sybase.com
• Ask for code