recite : sybase count char from 1 !!

let’s say ’05:00′, to get the hour, should be substring(1, 2)
to get the miniute should be substring(3, 2).

besides, char is fixed length is fixed length, for example char(5) would pad ‘5:00′ with ?? at end of ’00’

Advertisements

Nice wiki on Sybase SQL debugger

from http://www.petersap.nl/SybaseWiki/index.php?title=SQL-Debugger_-_DBA_usage

Sybase's sql-debugger (sqldbgr) is a tool included with the ASE installation and can be used to debug stored procedures and triggers. This might give you the impression that this tool is mainly for developers and not for DBA's. However, the debugger also allows you to attach to an already running process (another spid), examine or change local variables of that other session and even inspect their temporary tables. For production environments the tool provides just that extra information where Monitoring Tables fall short.

==Example of SQL code that needs to be debugged==
As an example the following code is created into a database. Please note that for readability things like transaction handling, error checking and compilation requirements have been left out. Description of functionality is given below.
 create procedure myProc @a int, @b int
 as
 
 declare @c int
 
 select @c = c_value
 from myTable
 where a_value = @a
 
 select d_value
 into #work
 from myOtherTable
 where b_value = @b
 
 execute myOtherProc @c
 go

 create procedure myOtherProc @c int
 as
 
 select *
 from BigTable
 join #work on (BigTable.d_value = #work.d_value)
 where c_column = @c
 go

When the myProc procedure is executed it retrieves a value from a table, populates a worktable and executes the stored procedure myOtherProc. The stored procedure myOtherProc selects data from the worktable #work and a very big table. Once this procedure is running it is impossible to determine the content of the temporary table at run-time (or a cumbersome procedure must be followed). Any inspection of local variables is not possible. Now this is where SQL-debugger kicks in.

==Starting the debugger==
You can start the debugger like this:
 $SYBASE/$SYBASE_ASE/bin/sqldbgr -U <username> -P <password> -S <host:portnumber>
The parameters username and password speak for themselves, host:portnumber should be substituted with the hostname (or IP-address) and the portnumber where your ASE server is listening on.

Make sure to put a space between the command switch and the argument, so put a space after -U, -P and the -S. Check that the SYBASE_JRE or JAVA_HOME environment variable has been set and points to a Java runtime environment.

Once you are logged on a prompt will be shown:
 (sqldbg)
To exit the debugger, just type 'quit'

==Attaching to a session==
To connect to another session you must know its spid. Within the debugger you can execute normal sql commands like sp_who, but you must put "mysql" in front of the statement.
Remember that you are working with a debugger, so the output of e.g. sp_who is not very nicely formatted and you can only type your command on a single line. No "go" is needed to execute the command within the debugger. Just press 'Enter'.
 (sqldbg) mysql sp_who
When you have determined the spid, you can connect to the session with the attach command:
 (sqldbg) attach <spid>
 (sqldbg)
When you get "You cannot debug a task that is not owned by you" the session is using another username than you. See below how to resolve this.
With the detach command you can detach from an attached session.
 (sqldbg) detach <spid>

==Retrieving the call stack==
When you have been attached successfully to the other spid you can see the call-stack from that session with the "where" command
 (sqldbg) where
 (dbo.myOtherProc::5::@c = 2)
 (dbo.myProc::17::@a = 1,@b = 3)
 (ADHOC::1::null)
 (sqldbg)
From this we can learn quite a lot:
* At the first line (dbo.myOtherProc::5::@c = 2) it is shown that the procedure dbo.myOtherProc is currently running, it is at line 5 within that stored procedure and a value of 2 has been passed into the @c parameter. Since this is the first line from the "where" output, the level is numbered as 0.
* At the second line (dbo.myProc::17::@a = 1,@b = 3) the procedure dbo.myProc is shown. This means that myProc called myOtherProc at line 17. Two variables were passed to the myProc procedure. Now we are at level 1.
* Finally, (ADHOC::1::null) tells us that the myProc procedure was called from a command line tool (like isql / SQL-Advantage), at line 1 of the batch. This is level 2.

==Viewing local variables==
Local variables can be viewed with the "show variables" command.
 (sqldbg) show variables
 (sqldbg)
In this example no output is shown. This is because "show variables" works default at level 0 and a distinction is made between variables declared within the stored procedure, and parameters that were declared in the "create procedure" statement. Indeed, no local variables were declared within the myOtherProc stored procedure and therefore "show variables" shows nothing.
 (sqldbg) show variables at level 1
 int @c 2
 (sqldbg)
With "show variables at level 1" we can actually see the declared local variables within the myProc procedure, their datatype and value. In this case @c was declared as an int and a value of 2 has been put into it.

Note: Global variables cannot be retrieved for an attached session. For instance, when the running procedure has changed the transaction isolation level this will be reflected in the @@isolation global variable. This change will not be seen within the debugger that has been attached to that particular session.

==Viewing temporary tables==
To view temporary tables use the "sql" command (not the mysql command), like this:
 (sqldbg) sql select * from #work
 d_value
 4
 (sqldbg)
Now we know that the attached session created a temporary table with just one row. The column d_value has a value of 4.
To select data from a temporary table you must know the name of the table as it is created within the stored procedure. In practice this means that you should have access to the source code of the stored procedure. Alternatively you can run a select on sysobjects in the temporary database and query the name column as in this example:
 select name from tempdb..sysobjects
The output will show the first few characters of the name of the table, followed by the spid.

There is a small problem when the attached session is using a temporary database and that database is not accessible by you. Such a situation can occur when the login/application of the attached session is bound to a specific tempdb. In that case you need to impersonate the other login with the 'setuser' command.

==Modifying local variables or temporary tables==
Local variables (as viewed with the "show variables" command) can be modified with the "set" command. This is only possible for variables at level 0. Example:
 (sqldbg) set @z = 2
Temporary tables can be modified with the "sql" command. Example:
 (sqldbg) sql delete from #work where d_value = 3
Although you are attached to a session this does not mean that you have taken over that session. Any locks set by the session will also affect the debugger. When a temporary table is locked exclusively it cannot be modified through the debugger. Selection of data is always possible, so these locks can indeed by bypassed.

==Resolving "You cannot debug a task that is not owned by you"==
When you try to attach to a session and the error "You cannot debug a task that is not owned by you" is raised, the session is running with another username than yourself. By default you can only attach to a session running with the same username as yourself.
Using the T-sql command "set session authorization" you can impersonate another user and then use the attach command. Before you can execute "set session authorization" you should have been granted privilege to it, even when you have already sa_role or sso_role. To get this privilege, a dba should add you to the master database as a user and then execute "grant set proxy to <your-username>" (preferably with the ‘restricted’ option). Improper usage of "grant set proxy to" can introduce security issues so please read and understand the Sybase documentation with regard to this before using it.
When all requirements have been met you can execute "set session authorization" within the debugger. Example:
 (sqldbg) attach 18
 You cannot debug a task that is not owned by you
 (sqldbg) mysql set session authorization 'joe'
 (sqldbg) attach 18
 (sqldbg)

==Further reading==
Sybase documentation for sqldbgr is here http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.utility/html/utility/utility216.htm

Sybase documentation for "grant" statement is here http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.commands/html/commands/commands59.htm

[[Category:ASE]]

PL/SQL exception handling

Working on our US project recently, which is actually one-tier, simply db access/manipulation using huge chunk of SP.

Exception handling become extremely importantly, which happens missed in our US SP.

Refer to http://plsql-tutorial.com/plsql-exception-handling.htm
for good and detailed explanation on exception handling. Sybase and Oracle PL/SQL are similar in such case.

blocks…
exception
when ..
when ..
else ..

Exception Handling

In this section we will discuss about the following,
1) What is Exception Handling.
2) Structure of Exception Handling.
3) Types of Exception Handling.
1) What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly. When an exception occurs a messages which explains its cause is recieved.
PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message
By Handling the exceptions we can ensure a PL/SQL block does not exit abruptly.
2) Structure of Exception Handling.

The General Syntax for coding the exception section

DECLARE

Declaration section

BEGIN

Exception section

EXCEPTION

WHEN ex_name1 THEN

-Error handling statements

WHEN ex_name2 THEN

-Error handling statements

WHEN Others THEN

-Error handling statements

END;

General PL/SQL statments can be used in the Exception Block.

When an exception is raised, Oracle searches for an appropriate exception handler in the exception section. For example in the above example, if the error raised is ‘ex_name1 ‘, then the error is handled according to the statements under it. Since, it is not possible to determine all the possible runtime errors during testing fo the code, the ‘WHEN Others’ exception is used to manage the exceptions that are not explicitly handled. Only one exception can be raised in a Block and the control does not return to the Execution Section after the error is handled.

If there are nested PL/SQL blocks like this.

DELCARE

Declaration section

BEGIN

DECLARE

Declaration section

BEGIN

Execution section

EXCEPTION

Exception section

END;

EXCEPTION

Exception section

END;

In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly with an error.
3) Types of Exception.

There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions
a) Named System Exceptions

System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.

For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.

Named system exceptions are:
1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.
Exception Name Reason Error Number

CURSOR_ALREADY_OPEN

When you open a cursor that is already open.

ORA-06511

INVALID_CURSOR

When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.

ORA-01001

NO_DATA_FOUND

When a SELECT…INTO clause does not return any row from a table.

ORA-01403

TOO_MANY_ROWS

When you SELECT or fetch more than one row into a record or variable.

ORA-01422

ZERO_DIVIDE

When you attempt to divide a number by zero.

ORA-01476

For Example: Suppose a NO_DATA_FOUND exception is raised in a proc, we can write a code to handle the exception as given below.

BEGIN

Execution section

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line (‘A SELECT…INTO did not return any row.’);

END;

b) Unnamed System Exceptions

Those system exception for which oracle does not provide a name is known as unamed system exception. These exception do not occur frequently. These Exceptions have a code and an associated message.

There are two ways to handle unnamed sysyem exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.

We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.

Steps to be followed to use unnamed system exceptions are
• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicity.
• To handle the exception explicity, they must be declared using Pragma EXCEPTION_INIT as given above and handled referecing the user-defined exception name in the exception section.

The general syntax to declare unnamed system exception using EXCEPTION_INIT is:

DECLARE

exception_name EXCEPTION;

PRAGMA

EXCEPTION_INIT (exception_name, Err_code);

BEGIN

Execution section

EXCEPTION

WHEN exception_name THEN

handle the exception

END;

For Example: Lets consider the product table and order_items table from sql joins.

Here product_id is a primary key in product table and a foreign key in order_items table.
If we try to delete a product_id from the product table when it has child records in order_id table an exception will be thrown with oracle code number -2292.
We can provide a name to this exception and handle it in the exception section as given below.

DECLARE

Child_rec_exception EXCEPTION;

PRAGMA

EXCEPTION_INIT (Child_rec_exception, -2292);

BEGIN

Delete FROM product where product_id= 104;

EXCEPTION

WHEN Child_rec_exception

THEN Dbms_output.put_line(‘Child records are present for this product_id.’);

END;

/

c) User-defined Exceptions

Apart from sytem exceptions we can explicity define exceptions based on business rules. These are known as user-defined exceptions.

Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception section.

For Example: Lets consider the product table and order_items table from sql joins to explain user-defined exception.
Lets create a business rule that if the total no of units of any particular product sold is more than 20, then it is a huge quantity and a special discount should be provided.

DECLARE

huge_quantity EXCEPTION;

CURSOR product_quantity is

SELECT p.product_name as name, sum(o.total_units) as units

FROM order_tems o, product p

WHERE o.product_id = p.product_id;

quantity order_tems.total_units%type;

up_limit CONSTANT order_tems.total_units%type := 20;

message VARCHAR2(50);

BEGIN

FOR product_rec in product_quantity LOOP

quantity := product_rec.units;

IF quantity > up_limit THEN

message := ‘The number of units of product ‘ || product_rec.name ||

‘ is more than 20. Special discounts should be provided.

Rest of the records are skipped. ‘

RAISE huge_quantity;

ELSIF quantity up_limit THEN

RAISE huge_quantity;

ELSIF quantity < up_limit THEN

v_message:= 'The number of unit is below the discount limit.';

END IF;

Dbms_output.put_line (message);

END LOOP;

EXCEPTION

WHEN huge_quantity THEN

raise_application_error(-2100, 'The number of unit is above the discount limit.');

END;

/

Nice summary for java jar: class path, index.list and manifest.mf

      08-18-2006
Hello everybody,

I’ve just spent some hours (late hours) fighting against the classpath
in JAR archives, the MANIFEST.MF file and the INDEX.LIST file.

I just want to mention together some of the information I gathered
through internet (and tested on my own). They may be useful to someone
else. I’m not completely sure to have understood everything, though.

1. You can’t launch a Java application using the “java” command, and
using both the -jar (specifies a jar file) option and the -cp
(specifies a classpath) option. They’re mutually exclusive. If you use
the -jar option, then the -cp is completely ignored.

2. You can add a ‘Class-Path’ entry in the manifest file, MANIFEST.MF
file (see http://java.sun.com/j2se/1.3/docs/guide/jar/jar.html). But
you have to be very cautious with it. Several rules apply in the way
you specify your class path:
– Class-Path line can’t be longer than 72 chars (nice one).
– You can break a classpath line into several, but you have to make
the line separation as CR[space][space] (see
http://bugs.sun.com/bugdatabase/view…bug_id=4295946)
– All classpath entries are relatives to the jar archive containing
the manifest.
– A single dot ‘.’ stands for the folder where the jar archive is
placed:
– Classpaths are separated by ‘ ‘ (one space).
– The classpath line must be finished by a carriage return (CR, LF,
or CRLF).

3. If there is a INDEX.LIST besides the MANIFEST.MF, then the class
path specified in the manifest is ignored. This can happen if some of
the jar libraries included in your jar have this INDEX.LIST file. When
you build your jar, you have to break up all jar libraries, and
recompile them into one big fat jar. Some (undesirable?) INDEX.LIST may
pop out to the META-INF folder.

The last point took me some time to figure out. In my case, the culprit
was mysql-connector-java-3.1.11-bin.jar.

Regards,
Mistake
Refers from http://www.velocityreviews.com/forums/t365098-manifest-mf-and-index-list.html.

Besides oracle doc on jar, Once the class loader finds a INDEX.LIST file in a particular jar file, it always trusts the information listed in it. If a mapping is found for a particular class, but the class loader fails to find it by following the link, an InvalidJarIndexException is thrown. When this occurs, the application developer should rerun the jar tool on the extension to get the right information into the index file.
Refer to http://docs.oracle.com/javase/1.4.2/docs/guide/jar/jar.html.

Cool Advise on Using DB cursor

from http://www.sql-server-performance.com/2007/cursors/

If possible, avoid using SQL Server cursors. They generally use a lot of SQL Server resources and reduce the performance and scalability of your applications. If you need to perform row-by-row operations, try to find another method to perform the task.

Here are some alternatives to using a cursor:

Use WHILE LOOPS
Use temp tables
Use derived tables
Use correlated sub-queries
Use the CASE statement
Perform multiple queries

More often than not, there are non-cursor techniques that can be used to perform the same tasks as a SQL Server cursor. [2000, 2005, 2008] Updated 1-29-2009

*****

If you do find you must use a cursor, try to reduce the number of records to process.

One way to do this is to move the records that need to be processed into a temp table first, then create the cursor to use the records in the temp table, not from the original table. This of course assumes that the subset of records to be inserted into the temp table are substantially less than those in the original table.

The lower the number of records to process, the faster the cursor will finish. [2000, 2005, 2008] Updated 1-29-2009

*****

If the number of rows you need to return from a query is small, and you need to perform row-by-row operations on them, don’t use a server-side cursor. Instead, consider returning the entire rowset to the client and have the client perform the necessary action on each row, then return any updated rows to the server. [2000, 2005, 2008] Updated 1-29-2009

*****

If you have no choice but to use a server-side cursor in your application, try to use a FORWARD-ONLY or FAST-FORWARD, READ-ONLY cursor. When working with unidirectional, read-only data, use the FAST_FORWARD option instead of the FORWARD_ONLY option, as it has some internal performance optimizations to speed performance. This type of cursor produces the least amount of overhead on SQL Server.

If you are unable to use a fast-forward cursor, then try the following cursors in this order, until you find one that meets your needs. They are listed in the order of their performance characteristics, from fastest to slowest: dynamic, static, and keyset. [2000, 2005, 2008] Updated 1-29-2009

*****

Avoid using static/insensitive and keyset cursors, unless you have no other choice. This is because they cause a temporary table to be created in TEMPDB, which increases overhead and can cause resource contention issues. [2000, 2005, 2008] Updated 1-29-2009

*****

If you have no choice but to use cursors in your application, try to locate the SQL Server tempdb database on its own physical device for best performance. This is because cursors may use the tempdb for temporary storage of cursor data. The faster your disk array running tempdb, the faster your cursor will be. [2000, 2005, 2008] Updated 1-29-2009

*****

Using cursors can reduce concurrency and lead to unnecessary locking and blocking. To help avoid this, use the READ_ONLY cursor option if applicable, or if you need to perform updates, try to use the OPTIMISTIC cursor option to reduce locking. Try to avoid the SCROLL_LOCKS cursor option, which reduces concurrency. [2000, 2005, 2008] Updated 1-29-2009

*****

When you are done using a cursor, don’t just CLOSE it, you must also DEALLOCATE it. Deallocation is required to free up the SQL Server resources used by the cursor. If you only CLOSE the cursor, locks are freed, but SQL Server resources are not. If you don’t DEALLOCATE your cursors, the resources used by the cursor will stay allocated, degrading the performance of your server until they are released. [2000, 2005, 2008] Updated 1-29-2009

*****

If it is appropriate for your application, try to load the cursor as soon as possible by moving to the last row of the result set. This releases the share locks created when the cursor was built, freeing up SQL Server resources. [2000, 2005, 2008] Updated 1-29-2009

*****

If you have to use a cursor because your application needs to manually scroll through records and update them, try to avoid client-side cursors, unless the number of rows is small or the data is static. If the number of rows is large, or the data is not static, consider using a server-side keyset cursor instead of a client-side cursor. Performance is usually boosted because of a reduction in network traffic between the client and the server. For optimum performance, you may have to try both types of cursors under realistic loads to determine which is best for your particular environment. [2000, 2005, 2008] Updated 1-29-2009

*****

When using a server-side cursor, always try to fetch as small a result set as possible. This includes fetching only those rows and columns the client needs immediately. The smaller the cursor, no matter what type of server-side cursor it is, the fewer resources it will use, and performance will benefit. [2000, 2005, 2008] Updated 1-29-2009

*****

If you need to perform a JOIN as part of your cursor, keyset and static cursors are generally faster than dynamic cursors, and should be used when possible. [2000, 2005, 2008] Updated 1-29-2009

*****

If a transaction you have created contains a cursor (try to avoid this if at all possible), ensure that the number of rows being modified by the cursor is small. This is because the modified rows may be locked until the transaction completes or aborts. The greater the number of rows being modified, the greater the locks, and the higher the likelihood of lock contention on the server, hurting performance. [2000, 2005, 2008] Updated 1-29-2009

*****

In SQL Server, there are two options to define the scope of a cursor. LOCAL and GLOBAL keywords in the DECLARE CURSOR statement are used to specify the scope of a cursor. A GLOBAL cursor can be referenced in any stored procedure or batch executed by a connection. LOCAL cursors are more secure as they cannot be referenced outside the procedure or trigger unless they are passed back to the calling procedure or trigger, or by using an output parameter. GLOBAL cursors must be explicitly deallocated or they will be available until the connection is closed. For optimum performance, you should always explicitly deallocate a cursor when you are done using it. LOCAL cursors are implicitly deallocated when the stored procedure, the trigger, or the batch in which they were created terminates. We can use LOCAL cursors for more security and better scope of the cursor in our application, which also helps to reduce resources on the server, boosting performance. Contributed by Nataraj Prakash. [2000, 2005, 2008] Updated 1-29-2009