BizTalk Artifacts Monitoring

I have to create jobs to monitor send ports, orchestrations and receive locations and this post will be update as i make progress.

Below is a list of things to remember and consider when creating these monitoring jobs:

• All BizTalk databases should not be considered as our usual databases and extreme caution should be practices when using them
• BizTalk installation has 4 databases BizTalkMsgBoxDb, BizTalkMgmtDb, BizTalkTrackingDb and SSODB
• These databases are dependent on each other and data is moved between them 
• Check on using WITH (READPAST): excludes locked rows from result set

To Be Cont....

SELECT TOP from @VAR and DISTINCT TOP @VAR

Simple and useful:

SELECT TOP (@MAX_REC_COUNT)
from DBO.MY_TABLE WITH (NOLOCK);

This way we can get the @max_rec_count from a config table instead of hard coding the record count to retrieve. This is useful when you can modify the config table from a UI instead of making code changes.

I wish there was a better way to get DISTINCT TOP @VAR from a query but unfortunately no better way than:

SELECT TOP @MAX_REC_COUNT 
FROM (
           SELECT DISTINCT COL1, COL2, ...COLN
           from DBO.MY_TABLE WITH (NOLOCK)
          ) XX;


HTH

Database and Instance in Oracle and SQL Server

http://channel9.msdn.com/posts/SQL-Server-Essentials-for-Oracle-DBAs-01-Database-and-Instance

Good and very informational video. Hope they don't mind me sharing knowledge :)

SP_HELPDB

sp_helpdb function returns information about that databases in the particular database server. Below is an example:

SP_EXECUTESQL expects NTEXT/NCHAR/NVARCHAR

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Pretty straight forward error, just change the datatype of the variable you are using to assign the dynamically generated string.

Hope this helps....

PL/SQL Collections

This is one of those topics i used to dread when i started out as a database developer, not anymore. But still from time to time i need to refresh my memory when it comes to collections:

I recently started reading a book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" and it describes collections in such a way that i have to make a post of it. It is precise, with an example and perfect read for me:

Below are a few pointers that i usually like to remember:

Associative Arrays (TABLE OF)

• AKA Index by tables
• Of course use an index by BINARY_INTEGER or PLS_INTEGER or VARCHAR2
• Sparse collection
• Need not be consecutive
• Unbounded - no upper boundary
• Collection is extended by assigning values to non existing index values
• Data can be deleted
• Start with FIRST method to access data

Nested Tables (TABLE OF)

• No index
• Unbounded colleciton
• Dense collection when creating collection
• Data can be deleted
• NEXT and PRIOR (?) helps access collection elements

VARRAYS (VARRAY OF)

• No index
• Bounded collection so cannot be extended above the specified limit
• Data cannot be deleted
• Dense data collection
• Consecutive data
• Start with FIRST method to access data

Methods that can be used with collections. Not all methods can be used with all collections:

• EXISTS - exists(v) - returns boolean
• DELETE - delete, delete(v1) and delete(v1,v8) - removes data
• COUNT - returns number
• TRIM - trim and trim (v) - removes data
• LIMIT - returns number
• EXTEND - extend, extend(v1) and extend(v1,v8) - adds NULL elements
• FIRST - returns index of first element
• LAST - returns index of last element
• PRIOR - prior(v) - returns index of prior element
• NEXT - next(v) - returns index of next element

Hope this helps....

Quick Tip #4

To see what SQL a session is running in the database, find the SPID for that user using sp_who2 and then pass it as an input to the dbcc command.


sp_who2

dbcc inputbuffer (:SPID)

NULL SELF Argument Is Disallowed - Oracle

ORA-30625: method dispatch on NULL SELF argument is disallowed 

Cause: A member method of a type is being invoked with a NULL SELF argument. 
Action: Change the method invocation to pass in a valid self argument.


Well i was trying to parse an XML with multiple namespaces and was using EXISTSNODE function. When traversing between nodes and subnodes i forgot to remove a subnode name and the XPATH formed was incorrect:

    l_xmltype := xmltype (l_doc) ;
    l_index := 1;
    v_count := l_xmltype.existsnode (
    'ns1:NewSubscriptionNotificationRequest/subscriptionInfo/subscriptionKey/subscriptionInfo [' || to_char (l_index
    ) || ']', v_namespaces);
    dbms_output.put_line ('------' || v_count);
    --
    WHILE l_xmltype.existsnode (
    'ns1:NewSubscriptionNotificationRequest/subscriptionInfo [' || TO_CHAR (l_index
    ) || ']', v_namespaces) > 0
    LOOP
        l_value := l_xmltype.extract (
        'ns1:NewSubscriptionNotificationRequest/subscriptionInfo [' || to_char (
        l_index) || ']/duration/text()', v_namespaces) .getstringval () ;
        dbms_output.put_line ('-------------> ' || l_value) ;
        --
        l_index := l_index + 1;

    END LOOP;

This error is same as the evil NULL POINTER EXCEPTION in JAVA....

Just correcting the subnode path (remove subscriptionInfo from EXISTSNODE) to represent right structure helped resolve this problem.

Many thanks to A_Non On XML for his detailed explanation and examples on this blog about XML parsing. I hope he does not mind a well deserved praise.