The Low and High Trailing Execution (LHTE) Stop Order
1
Category Archives: Software-Database
SQL Question – Use Case Statement or Cursors?
Are you split over whether to use a CASE statement or CURSORS in your SQL code?
This article will help clarify your dilemma.
First of all let’s agree that both methodologies are useful for performing actions on a database table based on processing one SQL data row at a time.
Now let’s look at the main characteristics of each methodology.
CURSORS
Looks more complicated. Support costs are higher.
Best when working with multiple tables and complicated algorithms.
Here is an example of inserting into a second table using the first table.
This rudimentary example processed 25,000 rows in 45 seconds.
CASE STATEMENT
Looks simpler. Easier to maintain.
Best with select and where clauses used on a single table.
Here is an example of inserting into a second table using the first table.
This rudimentary example processed 25,000 rows in 137 seconds.
CONCLUSION
Each methodology has its advantages and each can help you get where you are going.
Good SQL, good night.
Kelley’s Software Installation & Upgrade Rules
Recently a site was performing a software upgrade over the weekend. No big deal, everyone thought, because this happens thousands of times across the world every day.
Yours truly was on call to support the site if any issues arose.
Needless to say they called Sunday afternoon after message traffic had gotten stuck in their queues for several hours and queue refreshes had not made a difference.
What I learned about the site was amazing. They had performed not one, not two, but three software upgrades all at once. They only backed up the system at the beginning. They were the first site to go to this release.
Well, this prompted me to draft the following list of rules.
- Never install release 1.0 of anything.
- Never be the first site for any release.
- Always read the release notes and review the installation instructions.
- Always hold a readiness review meeting to answer questions.
- Never start an installation or upgrade without a backup of both your database and your system.
- Never install or upgrade more than two releases at a time.
- Always backup your databases before your second upgrade.
- Optionally backup your system before your second upgrade.
- Always backup both your database and your system after the last release.
- Always test the functionality mentioned in the release notes.
- Always examine all the log files for errors.
- Always hold a party when the smoke clears aka the system is stable.
Now I feel a whole lot better. Creating this list was quite a catharsis.
Let me know if you agree or have something to add.
Sybase SQL Challenge – Sum Of Max Values
--------DATA in Cat_Tbl---------------------------
Cat1 Cat2 Value
a e1 113
b e1 14
a e2 15
c e3 13
a e1 13
a e2 11
c e4 1
PROBLEM:
For each distinct value in Cat1, user wants to find maximum of Values grouped by Cat2 and then sum them up.
Step 1: User wants to extract the following rows for example where Cat1 = ‘a’.
Cat1 Cat2 Value
a e1 113
a e2 15
a e1 13
a e2 11Step 2: User wants to extract maximum corresponding to Cat1 and Cat2:
Cat1 Cat2 Value
a e1 113
a e2 15Step 3: User wants to sum the Values for Cat1.
Cat1 SumOfValueColumn
a 128
b 14
c 14
SOLUTION 1 USING TEMPORARY TABLE #Max_Cat_Tbl
select Cat1, Cat2, max(Value) Maxvalue
into #Max_Cat_Tbl from Cat_Tbl
group by Cat1, Cat2 order by 1, 2
select Cat1, sum(Maxvalue)
from #Max_Cat_Tbl
group by Cat1
order by Cat1
drop table #Max_Cat_Tbl
goSOLUTION 2 USING VIEW Max_Cat_Tbl_View
create view Max_Cat_Tbl_View ( Cat1, Cat2, Maxvalue ) as select Cat1, Cat2, max(Value) from Cat_Tbl group by Cat1, Cat2 select Cat1, sum(Maxvalue) from Max_Cat_Tbl_View group by Cat1 order by Cat1 go
Good SQL, good night.
How do you catch a log suspend error in Sybase?
You have two issues.
One is the UNIX/Linux script does not detect errors correctly.
Secondly the transaction log is filling up frequently.
First the script needs to have the standard output AND the error output go to your log file. When you use the > it is using file descriptor 1. When you want to capture error output you need to use 2> for file descriptor 2. So the command would look like the following.
isql > error.log 2> error.log
Better yet, use this.
isql 2>&1 > error.log
The previous command says to have file descriptor 2 go to file descriptor 1 which goes to a log file.
Now to detect errors, look for “Msg” which all Sybase errors have in front by using the grep command.
Secondly to resolve the transaction log space, you need to grow the size of the log which should be on its own device separate from data. Then you need to set the threshold to dump aka flush the completed transactions to a disk file automatically. You need to investigate the following commands to do this.
alter database, sp_helpthreshold, sp_addthreshold, sp_thresholdaction
Good SQL, good night.
Does Sybase support string types that it doesn’t right trim?
Yes.
For char columns to be fixed length, you must use the not null attribute.
For variable length columns, use varchar or char with a null attribute.
Then to measure the real data size including trailing spaces use the datalength function not the len function nor the charlength function.
Good SQL, good night.
Sybase Functionality
QUESTION:
Does anybody know if Common Table Expressions and User-defined functions (not from Java) are supported in Sybase?
ANSWER:
Microsoft SQL Server is based on Sybase 7.0. Sybase may not have Common Table Expressions (CTE) and User-Defined Functions, but there are equivalent ways to do the same thing.
For example CTE can be done either in nested queries or via temp tables using a number sign (#) in front of the table name.
For User Defined Functions, create a stored procedure with simple SQL code and call it via the exec function for example “exec my_sql_code”. This allows nesting of stored procedures.
Good SQL, good night.
Reverse Engineer Create Table Schema for Sybase or SQL Server
QUESTION: Is there any way to fetch the create table schema including the textual representation of actual Sybase datatypes (instead of the number)?
ANSWER: Yes
Sybase Query Analyzer – showplan
QUESTION:
I’m querying Sybase database from C# code. Is there any good query analyzer to understand the exact queries being generated like the Query Analyzer for Sql Server?
ANSWER:
You can set some Sybase variables to see the Query Plan such as the following:
set showplan on
set statistics io on
set statistics time on
exec my_proc (my_arg1, my_arg2, ...)
go
I have put this into a shell script to pass the command line arguments including stored procedure name and parameters.
isql -Uxxx -Pyyy <<-_EOF_
set showplan on
set statistics io on
set statistics time on
exec $*
go
_EOF_You use this to see if there are any table scans which should be avoided at all costs.
Good SQL, good night.
Sybase Error Codes
Problem: Need to distinguish between Sybase RDBMS related exceptions and user data exceptions .
Solution: Since Sybase uses error numbers less than 2000, you can choose to use numbers larger than 3000. You can control the returned error code by using the raiserror function. Yes that is not a misspelling.
Note: Sybase with many of its functions, tried to save keystrokes by eliminating duplicate characters. Here Sybase did not allow double “e”s but left the double “r”s in. Oh well.
For more info see http://stackoverflow.com/questions/7482383/distinguish-sybase-expections-types
Good SQL. Good night.
Michael E Kelley 