TechKnowLogic

Cross Domain Webix Post

2018-12-04T03:24:00.002-08:00

Recently, we were working with Spring Boot and Webix framework using Ajax. We had quite some trouble to POST data back to the Server with Spring Boot.

Error:

No 'Access-Control-Allow-Origin' header is present on the requested resource.

Origin 'http://localhost:7100' is therefore not allowed access.

We realized the problem was because of our AJAX cross-domain requests that are forbidden by default by the same-origin security policy.

Since browsers need to “preflight” any asynchronous or rather HTTP request methods that modify data and also fetch an “approval” the problem had to be addressed both way i.e. Server side and Browser

i) We had to enable the CORS assertion in Spring Boot i.e. @CrossOrigin(*)

ii) Enable Cross-origin resource sharing (CORS) from the Chrome browser. You need to add an extension or CORS enabler from the Chrome Web Store for this.

Thereafter using the following code the JSON data was post back to the server successfully.

webix.ajax().headers({

"Content-type":"application/json"

}).post("./data.php", JSON.stringify(payload), function(text){

//optional callback here

});

*** Do note, while enabling CORS resolves the problem but do not forget to switch off CORS from browser post verification. Otherwise, that might leave the site vulnerable to Cross Site Scripting issues (XSS) when the external site is compromised.

Plastic Solution

2018-12-04T02:29:00.000-08:00

যেখানে দেখিবে চাই উড়াইয়া দেখো তাই

The first plastic eating bacteria (Ideonella Sakaiensis 201-F6) was discovered in a Japanese waste dump.

Some Statistics:

Global Plastic Production: 8-10 Billion Tonnes per Year with only 8-10 % possible Recycling.

PET (polyethylene terephthalate): Natural degradation 450 years

6% of global oil production is devoted to the production of plastics.

Solution to the problem:

1) Ideonella Sakaiensis 201-F6 (Bacterium):

Produces enzymes to breakdown PET (PolyEthylene Terephthalate).

Scientists have already enhanced this natural enzyme and produced mutant enzymes that is far superior in the job.

2) Waxworm Caterpillars (Galleria Mellonella):

Each Waxworm can create an average of 2.2 holes per hour and break down plastic in a matter of hours—about a hundred waxworms could eat 96 milligrams of plastic in 12 hours, to be precise.

3) Mealworms:

Possess gut microbes that helps them to safely eat Stryofoams/Polysterene.

100 mealworms that consumed 34 to 39 milligrams of Styrofoam, which is about the weight of a pill, every day.

4) Fusarium OxySporum (Biocatalyst Fungus):

A Cutinase from the mesophilic fungus Fusarium oxysporum, named FoCut5a, was expressed either in the cytoplasm or periplasm of Escherichia coli BL21 that can modify plastic naturally.

Happiness Index

2018-12-04T02:16:00.001-08:00

Are you and your nation happy?

What is your GNH (Gross National Happiness) Index?

Keep an eye on the factors that governs happiness…i.e. Freedom of Choice, Trust, Generosity, GDP etc.…

https://en.wikipedia.org/wiki/Gross_National_Happiness

https://en.wikipedia.org/wiki/World_Happiness_Report

Denmark is the happiest nation with Israel at 13^th, USA at 14^th, UK at 23^rd and China at 83rd position (as in 2016).

India, at 118^th on GNH.

For some more information…..

There is also another statistical dispersion parameter called GINI:

GINI Coefficient governs the “the inequality among values of a frequency distribution (for example, levels of income)” or in other words i.e. “opportunity in-equality”, “income in-equality” etc.

The lower the GINI the better.

Australia leads with Israel at 16^th, China at 32^nd, UK at 33^rd and USA at 34^th (as per 2009 report).

India at 79^th on GINI.

Disabling Data Execution Prevention (DEP) for Windows 7 - Part I

2013-08-09T00:44:00.000-07:00

This is a general research notes on the new way Date Execution Prevention (DEP) in Windows 7 systems prevents many of the old applications getting executed on the system (mostly memory address areas). There are various details that can be looked into for in-depth knowledge on this issue. Truly speaking this is tight and there is no such workaround except ROP gadgets (Return - Oriented Programming) that is more of ethical hacking than a workaround and is a subject in itself.
The problem which usually comes up with is more technical and is related to diverse factors.

Actual Exception from Visual Studio:

This actual exception comes up while instantiating the new Java Axbridge ActiveX object.

System.AccessViolationException was caught Message="Attempted to read or write protected memory. This is often an indication that other memory is corrupt.

This followed up by NullReference exception as it failed to create the object.

Standalone Executable may come up with the following, though it points to the same:

System.Runtime.InteropServices.COMException (0x80040154):

Retrieving the COM class factory for component with CLSID {D824B185-AE3C-11D6-ABF5-00B0D07B8582} failed due to the following error: 80040154.

Research Areas:
1) 32 bit/64 bit application compatibility on Windows 7 (64 bit) system.
2) Oracle Java JRE support for Windows 7 system (since Java axbridge components were used).
3) Microsoft .NET component supports for 64 bit systems.
4) The concepts related to PAE (Physical Address Extension) is worth mentioning.

This post is an accumulation of important points and references of various links, that helped resolving the actual problem.

What was tried that did not help:

It is more or less pointing to compatibility issue where all the following did not work in our case:

Recompilation in 32 bit mode (both client and server that includes the Java interoperability DLL).
We verified that DEP (Data Execution Prevention) which is by default turned ON for all applications in Windows 7 does not actually contribute to this. Trying to enlist the application as a DEP exception did not help as Windows 7 did not allow that.
Re registering the components in Windows 7 for 64 bit.
Force converted the DLL to 32 bit using Corflags.exe (did that today forgot to mention).
Dump file analysis.
We copied the same executable from the Windows 7 machine to the Windows XP machine and upon execution there was no execution.

There are various issues with AxBridge Java ActiveX DLL gets invoked from 64 bits machines. These were also investigated in details. I have kept this article brief and is a part of the development/troubleshooting process.

The next part of this article will be released soon that talks about an indepth analysis and the actual fix.

Unix: List Files Which Do Not Have A Particular Pattern In Filename

2012-09-12T00:27:00.003-07:00

Many a times we want to list files which do not have a particular pattern in its file name.
Here are a few things you can do (Remember not to use ls -l here):

ls |grep -v bz2 -- file name has bz2 anywhere in the file name
ls |grep -v bz2$ -- file name ends with bz2
ls |grep -v ^bz2 -- file name starts with bz2
ls |grep -v .bz2. -- file name has bz2 in the middle
ls |grep ..bz2 -- file name has atleast 2 chars before bz2
ls |grep '\.bz2$' -- file name ends with .bz2 (\ to escape)
ls |grep "\.bz2$" -- file name ends with .bz2 ("or ' might matter with the shell being used)
ls |grep b.2 -- file name can have bz2 or bx2
ls |grep '\.b.2' -- file name can have .bz2 or .bx2
ls |grep "\.b.2" -- file name can have .bz2 or .bx2

Debug Methods Oracle (Coarse Grained Auditing)

2012-09-12T00:11:00.003-07:00

Scenario

There are many times, where there was an issue in production and you did not have any idea how it happened. The following article will give you some direction. Please note there are many other ways too. Currently, I will just focus on a method I had used during a similar situation.

This error information is from the latest logs from Production said:

ORA-00001: unique constraint (TABLE_ABCD.SYS_C006189415) violated

Now the constraints to troubleshoot the above scenario are:

You have limited access/privilege in production environment.
The above was due to a duplicate record that came in and your process tried to insert that duplicate data.

But what was that data that caused disruption? The answer could have been found out by using FlashBack Query feature from Oracle. This however is version specific. Let’s look into some other aspects of debugging.

Observation

There is no clue to the data that was being inserted at the time this exception was thrown.
Not much information either from any of the relevant log files about the metadata.
The respective process tables handle massive number of records which makes it further more cumbersome to troubleshoot or pin point the culprit.
No bulletin or logs from front end either.

Other Debug Options

In this scenario we have a few options to explore and figure out the corrupt data:

Putting ON our Application Debug option – Heavy On Performance & Resource (Not always helpful either)
Get the trace log files inspected by the DBA (if at all the relevant data is set to be captured) – Complex, Heavy On Performance & Resource
There are other DBMS packages like DBMS_ERRLOG, DBMS_MONITOR, DBMS_RLS etc. – Little More Coding and better with live capture instead of historical
Triggers to capture & hold the data. – Needs another table to be created to hold the bad data
There are some other ways as well not mentioned for now

A Simpler Option (for database issues)

Auditing, at Statement Level.

There are other variations to this as well like Object level, Privilege level, Fine Grained Auditing to increase the granularity of data captured.

PREREQUISITES: The system parameters, AUDIT_SYS_OPERATIONS are set to TRUE & AUDIT_TRAIL is set to DB_EXTENDED (This setting required to capture the SQL_BIND data). Check V$PARAMETER view. Since by default there will be a certain level of auditing enabled for any database, luckily these settings might pre-exist. In case it doesn’t, enhanced privileges will be required to modify them.

SWITCH ON AUDIT (FOR THE TABLE ONLY):

AUDIT INSERT, UPDATE ON <OWNER>.<TABLE_NAME>

BY ACCESS – Can be BY SESSION as well

WHENEVER NOT SUCCESSFUL;

CHECK RESULTS: Query the USER_AUDIT_TRAIL table and check the fields, SQL_BIND (the data of interest), SQL_TEXT (the query which caused the failure), ACTION and RETURN CODE (0 for Success)

e.g. select * from USER_AUDIT_TRAIL where owner = 'OWNER_NAME' and OBJ_NAME= 'TABLE_NAME';

The bound data from SQL_BIND column will provide the solution:

SWITCH OFF AUDIT: NOAUDIT INSERT, UPDATE

ON <OWNER>.<TABLE_NAME>

WHENEVER NOT SUCCESSFUL;

NOTE: The above is also an instance where we made use of a minimal elevated privilege.

Refer to the following links for more details:

http://www.oracle-base.com/articles/8i/auditing.php

http://www.oracle-base.com/articles/10g/auditing-10gr2.php

http://docs.oracle.com/cd/B10501_01/server.920/a96524/c25audit.htm

Oracle Version

2011-06-25T04:08:00.000-07:00

Requirement is simple; you want to retrieve/recover the Version Information of your currently installed Oracle Database. Let us discuss most of the ways of doing so in Oracle & SQL Server.

Here are some of the ways to get it:

1) DBA_REGISTRY (Table)

SELECT * FROM DBA_REGISTRY;

SELECT comp_id, comp_name, version, status, namespace, control FROM DBA_REGISTRY WHERE comp_name LIKE '%Oracle%' ORDER BY comp_name;

This will give you the db version along with all the other oracle components installed.

Here is the result:

COMP_ID	COMP_NAME	VERSION	STATUS	NAMESPACE	CONTROL
CATALOG	Oracle Database Catalog Views	9.1.0.3.0	VALID	SERVER	SYS
CATPROC	Oracle Database Packages and Types	9.1.0.3.0	INVALID	SERVER	SYS

2) V$VERSION (View)

SELECT * FROM V$VERSION

This is the result:

BANNER

Oracle Database 9G Enterprise Edition Release 9.1.0.3.0 - 64bi

PL/SQL Release 9.1.0.3.0 - Production

NLSRTL Version 9.1.0.3.0 - Production

3) DBMS_DB_VERSION.VERSION (Procedure)

In case you are using SQL Assistant from Oracle you need the Server output to be set.

SET SERVEROUTPUT ON

EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.VERSION);

EXEC DBMS_OUTPUT.PUT_LINE (DBMS_DB_VERSION.RELEASE);

In case you are using 3rd Party Vendors for SQL Assistant as Benthic Golden Software you can have an output like this:

Press F10 or select View à DBMS OUTPUT Window

Then run the above code by F7. You will have the DBMS_OUTPUT.PUT_LINE generated output in the following window:

The result of the above query will be as:

4) DBMS_UTILITY.DB_VERSION (Function)

DECLARE

mydb_version varchar2 (200);

mydb_compatibility varchar2 (200);

BEGIN

dbms_utility.db_version( mydb_version, mydb_compatibility );

dbms_output.put_line( mydb_version );

dbms_output.put_line( mydb_compatibility );

END;

Here is the result using Benthic Golden Software.

5) DATABASE_PROPERTIES (Table)

SELECT * FROM database_properties WHERE property_name LIKE '%RDBMS%';

Here is the result:

PROPERTY_NAME	PROPERTY_VALUE	DESCRIPTION
NLS_RDBMS_VERSION	9.2.0.3.0	RDBMS version for NLS parameters

SQL Server Version Information

1) SERVERPROPERTY()

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The result will be something like this:

10.0.1600.1

CTP

Developer Edition

SERVERPROPERTY can be used to extract other data as well including COLLATION, BUILD etc.

Refer to the following link for more details:

http://msdn.microsoft.com/en-us/library/ms174396.aspx

2) @@VERSION

SELECT @@VERSION

The result will be something like this:

Microsoft SQL Server 2008 (SP1) - 10.0.2411.0 (X64) Feb 21 2009

Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600)

Refer to the following link for more details:

http://support.microsoft.com/kb/321185/

Automate SAP R/3 Login with QTP

2011-06-11T05:37:00.000-07:00

Test automation for SAP R/3 applications using QTP has become quite an interesting topic and in absolute demand by most companies implementing SAP these days worldwide. As a result, it has become necessary to have, in hand, a ready library of functions which assist to implement QTP scripting for SAP R/3 application quickly and easily.

In this section, we will discuss how to login to SAP R/3 application using QTP and Vbscript when the user credentials are provided through an external spreadsheet. The steps towards building such script would be;

1) Close any existing excel process running on the system.

2) Open the spreadsheet where user credentials have been provided and read the

credentials.

3) Read the details like Server name, Client details and Language from the spreadsheet

4) Login to SAP.

5) Quit Excel process and close all open objects.

Following sections helps to understand the code used for each steps.

1) Close any existing excel process running on the system.

The SystemUtil.CloseProcessByName(“Excel.exe”) method can be used to do this. One needs to note that SystemUtil is a QTP reserved object and can be used only with QTP.

However if one needs to achieve this using vbscript, then the following code may be used. This block of code may also be used to close multiple processes.

myComputer = "."

‘Enter the list of processes in the array. These processes will be closed by the block of code.

myArrayOfTargetProcess = Array("EXCEL.EXE")

Set objWMIService = GetObject("winmgmts:" _

& "{impersonationLevel=impersonate}!\\" &myComputer & "\root\cimv2")

Set CollectionOfProcesses = objWMIService.ExecQuery("SELECT * FROM Win32_Process")

For Each IndividualProcess in CollectionOfProcesses

For Each TargetProcess In myArrayOfTargetProcess

If LCase(IndividualProcess.Name) = LCase(TargetProcess) Then

intReturn = IndividualProcess.Terminate

End If

Next

2) Open the spreadsheet where user credentials have been provided and read the

credentials. For this, use the following code block.

PathForLoginSpreadSheet = <enter the path for login spread sheet>

'Create an excel application

Set myExcelForLogin = CreateObject( "Excel.Application")

‘Open the file in the specified location

myExcelForLogin.WorkBooks.open(PathForLoginSpreadSheet)

Set ExcelWorkBook = myExcelForLogin.ActiveWorkbook

Set ExcelLoginSheet = ExcelWorkBook.Worksheets("Login")

3) Read the details like Server name, Client details and Language from the spreadsheet

Let’s assume that the data is entered in the second row of the excel sheet. Data for “Server”, “Client”, “User”, “Password” and “Language” are in the 2^nd, 3^rd, 4^th, 5^th and 6^th column of the spreadsheet respectively.

This code is in continuation with the previous blocks.

With ExcelLoginSheet

‘Read the values for “Server”, “Client”, “User”, “Password” and “Language”

SAPR3ServerName = .Cells(intRow,2)

SAPR3Client = .Cells(intRow,3)

SAPUserName=.Cells(intRow,4)

SAPPassword=.Cells(intRow,5)

UserLanguage = .Cells(intRow,6)

End With

4) Login to SAP.

Use the statement below:

SAPGuiUtil.AutoLogon SAPR3ServerName, SAPR3Client, SAPUserName, SAPPassword, UserLanguage

Please note that SAPGuiUtil.AutoLogon is a QTP reserved keyword.

5) Quit all created objects.

myExcelForLogin.Quit

Set ExcelWorkBook =Nothing

Set ExcelLoginSheet=Nothing

Set myExcelForLogin =Nothing

Automatic Replenishment ~ Warehouse Management (WM) in SAP

2011-06-10T23:58:00.000-07:00

This article is intended for SAP functional consultants to configure automatic replenishment of a particular storage type for transfer orders. Whenever the stock in a bin falls below the MIN value (predefined minimum quantity), it will trigger replenishment or transfer request (TR) which will in turn get converted into a transfer order, so that the workers in the warehouse can carry out the actual physical transfer of stock.

We have a provision of assigning a definite storage type from where the stock will be picked up for replenishment.

Step 1:

Material Master has to be maintained with Warehouse Views.

Under Warehouse Mgmt 2 Storage bin, Maximum bin Quantity, Minimum bin Quantity and Replenishment Quantity has to be maintained.

Configuration Changes:

Step 2:

Movement type 320 is copied from existing movement type 319 under Logistics Execution ->Warehouse management->Activities->Transfers->Define Movement Types.

Movement type 320 is configured as the following and saved. We can define SRC for the Source storage bin so that no interim storage bin is taken into consideration.

Step 3:

Replenishment Control is defined for the storage type under Logistics Execution ->Warehouse management->Activities->Transfers-> Define Replenishment Control for Storage Type and saved.

Dedicated printers can be set up under Logistics Execution ->Warehouse management->Activities->Define Print Control for a particular storage type and saved

Step 4:

One job needs to be scheduled for each report. The report jobnames are randomly defined:

Report
~~~~~~~

RLAUTA10

RLLNACH1
.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Hierarchical Query – Oracle 10g & SQL Server

2011-05-17T22:44:00.000-07:00

The issue is simple.

You want a migration of your database from Oracle to SQL Server.

Hierarchical queries with parent child relationship inside the same table can be a pretty interesting area. In SQL Server 2000, we do not have any straightaway method to do so (unlike in Oracle 10g), along with customized output, but the pain is reduced in higher versions of SQL Server (SQL Server 2005 & 2008). Let us look at the basics quiet SIMPLY.

Oracle 10g

I will create the FAMILY table to explain this. However, there is a couple of assumption.

a) The data in the FAMILY table does not belong to a person having fallen in love multiple times, resulting in multiple marriages, further resulting in multiple families. So, Sukhendu’s father and Rajkumar’s father is not the same person.

b) Here null as parent_id means simply I am using it for this example. Definitely the Greater Grand Father also had his father.

However, be informed that Oracle can take care of a person having multiple families as well.

Person_ID	Person	Parent_ID
5	Sukhendu’sGreaterGrandFather	null
4	Sukhendu’sGreatGrandFather	5
3	Sukhendu’sGrandFather	4
2	Sukhendu’sFather	3
1	Sukhendu	2
0	Sukhendu’sBrother	2
55	Rajkumar’sGreaterGrandFather	null
44	Rajkumar’sGreatGrandFather	55
33	Rajkumar’sGrandFather	44
22	Rajkumar’sFather	33
11	Rajkumar	22
99	Rajkumar’sSister	22

LEVEL

Do have a look at this query.

SELECT person_id, LPAD (' ', LEVEL*3) ||Person as Person, parent_id, LEVEL

FROM family

START WITH parent_id IS NULL

CONNECT BY PRIOR person_id = parent_id;

Result:

Person_ID	Person	Parent_ID	LEVEL
5	Sukhendu’sGreaterGrandFather	null	1
4	Sukhendu’sGreatGrandFather	5	2
3	Sukhendu’sGrandFather	4	3
2	Sukhendu’sFather	3	4
1	Sukhendu	2	5
0	Sukhendu’sBrother	2	5
55	Rajkumar’sGreaterGrandFather	null	1
44	Rajkumar’sGreatGrandFather	55	2
33	Rajkumar’sGrandFather	44	3
22	Rajkumar’sFather	33	4
11	Rajkumar	22	5
99	Rajkumar’sSister	22	5

The topmost in the hierarchy gets the LEVEL 1 with its children designated as per hierarchy. LPAD introduces the blank space for tabbing the names as per LEVEL.

START WITH defines the root row(s) of the hierarchy.
CONNECT BY defines the relationship between parent and child in the hierarchy.
PRIOR is a unary operator. PRIOR can be on either side of the equality operator. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

SYS_CONNECT_BY_PATH function gives you the path from root to node (or topmost to bottom most in the hierarchy).

SYS_CONNECT_BY_PATH

SELECT person_id, LPAD (' ', LEVEL*3) ||Person as Person, LEVEL, SYS_CONNECT_BY_PATH (person,'/') PATH

FROM family

START WITH parent_id IS NULL

CONNECT BY PRIOR person_id = parent_id;

Person_ID	Person	LEVEL	PATH
5	Sukhendu’sGreaterGrandFather	1	/ Sukhendu’sGreaterGrandFather
4	Sukhendu’sGreatGrandFather	2	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather
3	Sukhendu’sGrandFather	3	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather
2	Sukhendu’sFather	4	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather
1	Sukhendu	5	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu
0	Sukhendu’sBrother	5	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu’sBrother
55	Rajkumar’sGreaterGrandFather	1	/ Rajkumar’sGreaterGrandFather
44	Rajkumar’sGreatGrandFather	2	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather
33	Rajkumar’sGrandFather	3	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather
22	Rajkumar’sFather	4	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather/ Rajkumar’sFather
11	Rajkumar	5	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather/ Rajkumar’sFather/ Rajkumar
99	Rajkumar’sSister	5	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather/ Rajkumar’sFather/ Rajkumar’sSister

CONNECT_BY_ISLEAF

SELECT person, CONNECT_BY_ISLEAF ‘isLEAF?’, SYS_CONNECT_BY_PATH (person,'/') path

from family

START WITH parent_id IS NULL

CONNECT BY PRIOR person_id = parent_id;

Person	isLEAF?	PATH
Sukhendu’sGreaterGrandFather	0	/ Sukhendu’sGreaterGrandFather
Sukhendu’sGreatGrandFather	0	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather
Sukhendu’sGrandFather	0	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather
Sukhendu’sFather	0	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather
Sukhendu	1	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu
Sukhendu’sBrother	1	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu’sBrother
Rajkumar’sGreaterGrandFather	0	/ Rajkumar’sGreaterGrandFather
Rajkumar’sGreatGrandFather	0	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather
Rajkumar’sGrandFather	0	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather
Rajkumar’sFather	0	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather/ Rajkumar’sFather
Rajkumar	1	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather/ Rajkumar’sFather/ Rajkumar
Rajkumar’sSister	1	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather/ Rajkumar’sFather/ Rajkumar’sSister

CONNECT_BY_ROOT

SELECT person, CONNECT_BY_ROOT person ‘Origin’, SYS_CONNECT_BY_PATH (person,'/') path
FROM family
START WITH parent_id is null

CONNECT BY PRIOR person_id = parent_id;

Person	Origin	PATH
Sukhendu’sGreaterGrandFather	Sukhendu’sGreaterGrandFather	/ Sukhendu’sGreaterGrandFather
Sukhendu’sGreatGrandFather	Sukhendu’sGreaterGrandFather	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather
Sukhendu’sGrandFather	Sukhendu’sGreaterGrandFather	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather
Sukhendu’sFather	Sukhendu’sGreaterGrandFather	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather
Sukhendu	Sukhendu’sGreaterGrandFather	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu
Sukhendu’sBrother	Sukhendu’sGreaterGrandFather	/ Sukhendu’sGreaterGrandFather/ Sukhendu’sGreatGrandFather/ Sukhendu’sGrandFather/ Sukhendu’sFather/ Sukhendu’sBrother
Rajkumar’sGreaterGrandFather	Rajkumar’sGreaterGrandFather	/ Rajkumar’sGreaterGrandFather
Rajkumar’sGreatGrandFather	Rajkumar’sGreaterGrandFather	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather
Rajkumar’sGrandFather	Rajkumar’sGreaterGrandFather	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather
Rajkumar’sFather	Rajkumar’sGreaterGrandFather	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather/ Rajkumar’sFather
Rajkumar	Rajkumar’sGreaterGrandFather	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather/ Rajkumar’sFather/ Rajkumar
Rajkumar’sSister	Rajkumar’sGreaterGrandFather	/ Rajkumar’sGreaterGrandFather/ Rajkumar’sGreatGrandFather/ Rajkumar’sGrandFather/ Rajkumar’sFather/ Rajkumar’sSister

CONNECT_BY_ISCYCLE

Now let us assume that, Rajkumar is the father of his real father Rajkumar’sFather (whose real name is XYZ suppose). I know it’s hard to imagine, but hypothetically this definitely happens when your father is aged and looking up to you for all kind of support, guidance and warmth you got during childhood. Then you do the parental role. If this kind of looping occurs in your table then it can be fairly achieved through the pseudocolumn, CONNECT_BY_ISCYCLE, which will evaluate to"1", in these circumstances ( due to looping inside the table as explained later), along with NOCYCLE. Let me redefine the family table as FAMILY_1 and eliminate the Sukhendu subgroup.

UPDATE family_1

SET parent_id = 11 ~ Rajkumar

WHERE person_id = 22 ~ Rajkumar’sFather

Person_ID	Person	Parent_ID	isLOOP?
55	Rajkumar’sGreaterGrandFather	null	0
44	Rajkumar’sGreatGrandFather	55	0
33	Rajkumar’sGrandFather	44	0
22	Rajkumar’sFather	11	0
11	Rajkumar	22	1
88	Rajkumar’sDaughter	11	0
99	Rajkumar’sSister	22	0

The query for the above table is as follows:

SELECT person_id, person, parent_id, CONNECT_BY_ISCYCLE ‘isLOOP?’
FROM family_1
START WITH parent_id IS NULL
CONNECT BY NOCYCLE PRIOR person_id = parent_id;

SQL SERVER 2000

In SQL Server 2000 the derivation of a hierarchical result set is simple, but the customizations which are often required for a hierarchical display of data are very much difficult.

SELECT f1.person_id, f1.person, f1.parent_id, f2.person AS PARENT

FROM family f1

LEFT JOIN family f2 ON f1.parent_id = f2.person_id

SQL SERVER 2005

We are very much aware of Common Table Expressions (CTE) introduced in the later versions of SQL Server 2000. These work very efficiently in case of recursive queries. Although there can be much complex implementation of the CTE, the simplest can take care of the hierarchy issue with lot more customizations than in SQL Server 2000. We can address the complications later but for the time being let us just look at the query which can handle the same with the LEVEL description.

WITH FamilyHierarchy (parent_id, person_id, person, LEVEL)

(

-- Identity Description

SELECT f.parent_id, f.person_id, f.person,

0 AS LEVEL

FROM family AS f

WHERE parent_id IS NULL

UNION ALL

-- Hierarchical Identity Description

SELECT f.parent_id, f.person_id, f.person,

LEVEL + 1

FROM family AS f

INNER JOIN FamilyHierarchy AS fa

ON f.parent_id = fa.person_id

)

-- Statement that executes the CTE

SELECT fa.parent_id, fa.person_id, fa.person, LEVEL

FROM FamilyHierarchy AS fa

;

VB and VB.NET - Difference

2011-04-27T08:01:00.000-07:00

This is an old issue but very frequently we come across the same query. Particularly when we require the application (OCX) migration from VB.

What are some of the basic differences between VB & VB.NET?
Answer:

The following section enlists some of the issues I came across when encountered with a similar migration requirement. These differences are mostly in reference to VS2005.

1. VB.NET provides managed code execution that runs under the Common Language Runtime (CLR) resulting in robust, secure and stable applications. All features of the .NET framework are readily available in VB.NET.

2. VB.NET is entirely object oriented. This is a major addition that VB6 didn’t have.

3. The .NET framework comes with ADO.NET, which follows the disconnected paradigm, i.e. once the required records are fetched the connection no longer exists. It also retrieves the records that are expected to be accessed in the immediate future. This enhances Scalability of the application to a great extent.

4. Error handling has changed in VB.NET. The Try-Catch-Finally block has been introduced to handle errors and exceptions as a unit, allowing appropriate action to be taken at the place the error occurred thus discouraging the use of ON ERROR GOTO statement. Code maintainability is enormously helped with this addition.

5. The Event Log Model, OLE automation and DDE is not available in VB.NET. We need to use other kind of inter-process communication instead of DDE & OLE.

6.The properties, events and attributes for Form, Controls (list box, command button i.e. mask colour property etc.) has undergone a comprehensive change. It is better to refer to the msdn for further details. eg. The FileCopy replaced by File.Copy, Kill method replaced by File.Delete etc.

7. The property bag approach to persist data between various instances in VB is completely taken over by the Serialization concept.

8. VB.NET is free threading as opposed to the VB single-threaded apartment feature. In many situations developers need spawning of a new thread to run as a background process and increase the usability of the application. VB.NET allows developers to spawn threads wherever they feel like, hence giving freedom and better control on the application.

9. Security has become more robust in VB.NET. In addition to the role-based security in VB6, VB.NET comes with a new security model, Code Access Security (CAS). This security controls on what the code can access. For example you can set the security to a component such that the component cannot access the database. The CASPOL.EXE is the application with which this can be controlled to the widest extent. This type of security allows building components that can be trusted to various degrees.

10.The CLR takes care of garbage collection i.e. the CLR releases resources as soon as an object is no more in use. This relieves the developer from thinking of ways to manage memory. CLR does this for them.

11. VB 6.0 supported Null propagation — when Null was used in an expression, the result of the expression would also be Null. Null propagation is no longer supported in VB.NET.
In Visual Basic 6.0, the Null keyword indicated that a variable contained no data and the IsNull function was used to test for Null. In Visual Basic .NET, the Null keyword is a reserved word and has no syntactical value. The IsNull function is no longer supported.
Null is converted to DBNull, and IsNull is converted to IsDBNull. The behavior of DBNull is slightly different than that of Null. Null could be used in functions and assignments; DBNull cannot.

SEO - Directory Submission

2011-04-17T06:51:00.000-07:00

It was very recent days that I became very interested in writing myself down. Now, in todays world it is very much presumable that I would do the entire writing stuff online. Definitely being involved with IT related work, it is very much expected from me as well. In the above, process I noticed the importance of SEO and its one of the techniques called Directory Submission.
A short note of my understanding on this topic.

Introduction

The key objective of every web site owner is to increase the amount of traffic directly received. This traffic is increased by improving their rank with search engines like as Google and Yahoo! The search engines determine the position of the website in a search on the basis of a formula. One of the key factors of this formula is the number of back links a web site has. The quality of incoming links improves the search engine rankings and internet visibility of a website. Directory submission is a proven method for gaining quality one-way incoming back links.

Use of Directory Submission

Directory submission is a SEO technique that helps in generating online traffic to a website. It works by gaining quality backlinks and higher positions in the SERPs. It is one of the most vital parts of the link building campaign. The process works by submitting to a directory by filling out a few forms. Thereafter, directory owner either accepts or rejects the entry. Usually, the entry is accepted. After acceptance, the directory owner provides a quality back link for that web site.

Ways of Directory Submission

Automated Directory Submission:

An easier way of doing this is through automated directory submission, which can be done through tools. For example, an automatic directory submission tool named DirectorySubmissionTools.com provides one-click directory submission to hundreds, even thousands of directories. However, it charges a relatively small fee like USD 9.99 per credit to submit to 1,400 directories.

Some of the downfalls of automated directory submission are:

1. Automated submission often puts websites in wrong categories. This effects search engine ranking badly.

2. Automated submission misses on some site submission verification codes. These are usually those in top directories.

3. Automated submission software submits in many directories at one time. This may get the website banned by the search engines for spamming.

4. Most automated submission software are not able to support different title and description.

5. Automated submission usually does not check if a directory it submits to, is still alive or dead. This may result in submission in dead directories.

Manual Directory Submission:

The manual submission is more difficult, however it is the better option as many web directory sites does not allow automated submissions from software. Such web directory sites ensure that only real human beings make such postings on their directory.

Types of Directories for Submission

· Paid Directory Submission: A paid web directory provides premium service with faster approval times. These sites either deliver high quality traffic or good PageRank. Usually, these are less crowded providing greater chance to a link to achieve higher PR page. A list of paid directories can be found at:

http://www.best-web-directories.com/paid-directories.htm

· Niche Directories: Submitting a website to a niche directory provides a link from related niche with relevant content. A list of niche directories can be found at: http://www.directorycritic.com/niche-directories.html

· Free Directory Listing: A comprehensive list of free directories can be found at: http://www.best-web-directories.com/free-directories.htm

Benefits of Directory Submission

Some of these benefits of Directory Submission are:

· Increase of Backlinks: The main purpose of directory submission is link building. The increase of backlink is the biggest benefit of web directory submissions.

· Search Engine Indexing: Search engines crawl to the website by following the back links on the directory sites. This increases the chance of the website being indexed in search engine databases.

· Frequent Bot Visit: In order to archive, search engine robots visit all the sites on the web to gather information. Increase in the number of backlinks increases the frequency of the visits of search engine robots. This improves the internet visibility of the website by enhancing its rank.

· Increase in online traffic: Many people browse web directories for relevant contents with specific keywords or search terms. This takes them to the listed websites with related contents. The listing of website increases the internet visibility.

· Anchor Text: A desired anchor text can be used as a tile which is eventually hyperlinked to the website. This provides the website an anchor text backlink from the directories. Directory submissions using the anchor text as keyword increases link popularity.

· Link Exchange Partner: Web directories are the best place to find link exchange partners. Here related sites are categorised. By visiting related sites one can check if they have any link exchange programs. However, link exchange is not used by many webmasters these days due to its reduced effusiveness.

Teradata - Month End Date

2011-04-11T01:43:00.000-07:00

Sql Server:
Calculating the last day of the month in SQL Server is pretty simple.
Run the following piece of code and its done.
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
CurrentMonthEnd_Day
ResultSet:
CurrentMonthEnd_Day
———————–
2010-08-31 23:59:59.000

Teradata:
However, with reference to Teradata SQL, in our day to day queries in a datawarehouse environment very frequently we need to play with the month end dates that are mostly kept in the date dimension table with one field, i.e. the month end date field.
You might require to fetch the month end date which is 4 years back. usually the simplest way in this case will be to calculate 48 months back from the current month end date (in case the monthe end date that long is not stored into the database). This is fair. The piece of code required to do is simple.
SELECT (ADD_MONTHS(CAST('2012/08/31' AS DATE),-48))
Result:
2008/08/31
Now trying this simple thing will not work if its a leap year anywhere:
i.e. SELECT (ADD_MONTHS(CAST('2012/02/28' AS DATE),-48))
Result:
2008/02/28 -- Wrong.
Solution?? simple.
SELECT (ADD_MONTHS(CAST('2012/02/28' AS DATE) + 1,-48)-1)
Result:
2008/02/29 -- Correct.
This would definitely work with others. e.g.
SELECT (ADD_MONTHS(CAST('2012/08/31' AS DATE)+1,-48)-1)
Result:
2008/08/31

Managing Memory

2011-04-09T22:00:00.000-07:00

Let us discuss on some on some aspects of performance tuning and optimization of sql queries. The following terms are quiet relevant in this regard. Although we do not always think about these counters unlesss we are seriously having issues with the sql server memory or the performance of a strong query.
Buffer Cache Hit Ratio
The buffer cache is a syndicate or collection of buffer pages. The data pages occupies the biggest part of the memory and pages are mostly read into this cache. In OLTP systems this counter value should be as high as possible. Ideally, 99 percent in most cases. It is exactly the reverse in OLAP or DW environment where cache requirement although is there but as most of it is bulk data (billions of data records), cache requirement is less. Also frequency of requests coming in can be much lower than e.g. transactions in bank. If Buffer cache hit ratio value is low that means, only a few requests could be served out of the buffer cache, with the rest of the requests being served from disk.
If this truly happens, then definitely the SQL Server is warming up or the memory requirement of the buffer cache is more than the maximum memory available.
Lazy Writes/Sec
The Lazy writes/sec counter records the number of buffers written each second by the buffer manager's lazy write process. In this process the aged buffers are removed from the memory and the memory is freed up for other usage. The Lazy writes/sec values should consistently be less than 20 for the average system. A dirty, aged buffer is one that has all the changes and should be written to the disk. Higher values on this counter possibly indicate I/O issues or even memory problems.
Page Life Expectancy
Page Life Expectancy indicates how long a page will stay in the buffer syndicate without being referenced. A low number for this counter means that pages are being removed from the buffer. This lowers the efficiency of the cache and indicates the possibility of memory stress. On reporting systems, as opposed to OLTP systems, this number may remain at a lower value since more data is accessed from reporting systems. A reasonable value to expect to see here is 250 - 300 seconds or more.
Checkpoint Pages/Sec
The Checkpoint Pages/sec counter represents the number of pages that are moved to the disk by the checkpoint operation. These numbers should be low e.g. less than 30 per second for most systems. A higher number means more pages are being marked as dirty in the cache. When a page is modified in the memory it gets treated as a dirty page and is written into the disk by the next checkpoint operation. I/O problems might be a result of high values on this counter (which indicates a larger number of writes occurring within the system).

You can refer to the following thread or read more on how to fetch these values/counters

http://sql-server-performance.com/Community/forums/p/9782/53876.aspx

Technology Day @Microsoft

2011-04-08T07:11:00.000-07:00

It was some long time back..almost 2 years back I attended a session from my company. I understand technology is travelling to fast for us to keep par with. Still a small note:

Microsoft Innovation Day

(Full Day Session)

Topics:

v SaaS (Software as a Service)

v Cloud Computing

v Ajax

v Query Tuning

v Debugging tools with .NET

v Live Mesh

v Internet Explorer 8

1. SaaS (Software as a Service)

The key characteristics of SaaS software, according to IDC, include:

a) Network-based access to, and management of, commercially available software.

b) Activities are managed from central locations rather than at each customer's site, enabling customers to access applications remotely via the Web.

c) Application delivery, typically is closer to a one-to-many model (single instance, multi-tenant architecture) than to a one-to-one model, including architecture, pricing, partnering, and management characteristics.

d) Centralized feature updating, which obviates the need for downloadable patches and upgrades.

e) SaaS is often used in a larger network of communicating software - either as part of a mash up or as a plug-in to a platform as a service. Service oriented architecture is naturally more complex than traditional models of software deployment.

f) SaaS applications are generally priced on a per-user basis, sometimes with a relatively small minimum number of users and often with additional fees for extra bandwidth and storage. SaaS revenue streams to the vendor are therefore lower initially than traditional software license fees, but are also recurring, and therefore viewed as more predictable, much like maintenance fees for licensed software.

Software as a Service (SaaS, typically pronounced 'sass') is a model of software deployment where an application is hosted as a service provided to customers across the Internet. By eliminating the need to install and run the application on the customer's own computer, SaaS alleviates the customer's burden of software maintenance, ongoing operation, and support. Conversely, customers relinquish control over software versions or changing requirements; moreover, costs to use the service become a continuous expense, rather than a single expense at time of purchase. Using SaaS also can conceivably reduce that up-front expense of software purchases, through less costly, on-demand pricing.

2. Cloud Computing

An internet scale cloud services platform hosted in MS Datacenters, which provides an operating system and a set of developer services that, can be used individually or all together. The Azure platform is a similar kind of technology provided by Microsoft using Cloud Computing. Some examples are WindowsLive, OfficeLive and CRM Online etc.

Cloud Computing is a paradigm in which information is permanently stored in servers on the Internet and cached temporarily on clients that include desktops, entertainment centers, table computers, notebooks, wall computers, handhelds, sensors, monitors, etc.

3. Ajax

Webslice is an integral part of AJAX. This enables publishers to mark up or subscribe a part of a web page, allowing you to monitor your favourite dynamic pieces of the web. Thus on an AJAX developed page you need not press F5 to refresh the whole page every time you need to update.

The other key features are:

ü Script Combination

ü History Support

ü REST services

ü MVC & Dynamic data support

ü jQuery – Independent Library

ü Flexible DOM Manipulation

4. Query Tuning

a) Coding Issues

ü Excessive scanning

ü Poor / Missing Indexes

ü Out of date table statistics

ü Non selective triggers

ü Where clause not limiting record set

ü Excessive recompiles

ü Long blocking durations

b) WHERE Clause

ü Limit number of columns

ü Avoid NOT, OR

ü Avoid redundant criteria

ü Avoid calculated columns

ü Avoid functions

ü Avoid pattern search

c) JOINS

ü Avoid join hints

ü Join on indexed columns only

ü Avoid bookmark lookups

ü Avoid linked servers

ü Select criteria from table with fewest records

5. Debugging tools with .NET

The managed code in .NET environment as well as the unmanaged code interacting with the operating system and the machine (hardware) can be well debugged using these tools. The ILAsm and ILDasm are very powerful in debugging the .dll files. The SoS can help in detecting memory leaks very efficiently.

ü NGEN

ü SoS

ü ILAsm

ü ILDasm

ü Fusion Viewer

ü PEVerify

ü FxCop

ü JUnit

6. Live Mesh

The system is an implementation of cloud computing technology. You can keep your computable electronic devices all synchronized using this mesh. Thus, whether you are on your system or mobile you can keep all your devices in synch. Log on to http://www.mesh.com/ to know more about this.

Create a windows live profile, in case you do not have already and login to see the magic.

7. Internet Explorer 8.0

The key features are:

ü IE7 Emulation mode

ü Conditional Commenting

Open any HTML page, right click and view source. In case it’s compatible with IE 7.0 and you are unable to view the page on IE 8.0, add the following tag.

<HEAD>

<Meta content = “IE = 7” http – equiv = “X-UA-Compatible>

ü Option Model for IE 7.0

ü Developer Tools and Toolbar

ü Switching between IE 7.0 to IE 8.0

ü Accelerators (Advanced & Customized) on simple right click on any text.

*************************** Thank You *************************************** gf42725695101

Automating stored procedure execution

2011-04-06T06:54:00.001-07:00

The sp_procoption system stored procedure can be used to mark the user stored procedure for automatic execution when the SQL Server starts. Only objects in the master database owned by dbo can have the startup setting changed and this option is restricted to objects that have no parameters.
USE master
EXEC sp_procoption 'indRebuild', 'startup', 'true'

Select count(*)

2011-04-06T06:36:00.000-07:00

Select COUNT(*) from a table usually does a full table scan to return the total row count. The time taken can be considerable for large tables. Instead it is advisable to use the other approach in most cases. You can use sysindexes system table. The ROWS column in the sysindexes table contains the total row count for each table in your database. So, you can use the following select statement instead of SELECT COUNT(*):

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID('table_name')
AND indid < 2
The speed can be improved manifold.

Data Mocking & Data Masking

2011-04-05T07:33:00.000-07:00

I am currently working on an article as an anecdote to Data Mocking (what we call Mockups) and Data Masking. In the database world we, very frequently come up with these terms. I can understand that, you guys being database enthusiasts might be very much familier with these terms. However, not everyone would be like you all and the target audience for the article would be those guys.

SQL Optimizer Parameterization

2011-04-03T11:10:00.000-07:00

Plan Recompilation

Once the Query Optimizer has come up with a plan, which may have taken a considerable amount of work, SQL Server does its best to ensure that you can leverage all that costly work again. It does this by caching the plan it just created, and taking steps to ensure that the plan is reused as widely as possible. It does this by using parameterization options.

Parameterization

Parameterization is a process whereby SQL Server will take the T-SQL you entered and look for ways to replace values that may be variables with a token, so that if a similar query is processed, SQL Server can identify it as being the same underlying query, apart from some string, or integer values, and make use of the already cached plan. For example, the following is a basic T-SQL query to return data from the Adventure Works 2008 database:

   select *

   from person.person

   where lastname = 'fluffy'

The parameterization of this query would result in the string ‘fluffy’ being replaced with a parameter such that if another user executes the following query, the same plan would be used, saving on compilation time:

   select *

   from person.person

   where lastname = 'jonathan'

Note that this is just an example, and this particular query gets a trivial plan, so it isn't a candidate for parameterization.

The SQL Server Books Online topic on "Forced Parameterization" contains a lot of very specific details about what can and cannot be converted to a parameter.

To determine whether a query has been parameterized, you can search for it in the DMV sys.syscacheobjects (after first executing the query to ensure it is cached). If the SQL column of this DMV shows that the query has been parameterized, you will see that any literals from the query have been replaced by variables, and those variables are declared at the beginning of the batch.

Parameterization is controlled by the SQL Server configuration options — simple or forced:

§ Simple Parameterization: The default operation of SQL Server is to use simple parameterization on all queries that are suitable candidates. Books Online provides numerous details about which queries are selected and how SQL Server performs parameterization. Using simple parameterization, SQL Server is able to parameterize only a relatively small set of the queries it receives.

§ Forced Parameterization: For more control over database performance, you can specify that SQL Server use forced parameterization. The Forced parameterization option forces SQL Server to parameterize all literal values in any select, insert, update, or delete statement queries. There are some exceptions to this, and these are well documented in SQL Server Books Online. Forced parameterization is not appropriate in all environments and scenarios. It is recommended that you use it only for a very high volume of concurrent queries, and when you are seeing high CPU from a lot of compilation/recompilation. If you are not experiencing a lot of compilation/recompilation, then forced parameterization is probably not appropriate. If you use forced in the absence of these symptoms, you may end up with a degradation in performance and/or throughput, as SQL Server takes more time to parameterize a lot of queries that are not then reused.

Forced parameterization can also be more finely controlled through the use of plan guides. You will learn about plan guides in more detail later in this chapter.

What is Normalization??

2011-04-03T05:44:00.000-07:00

Normalization in the simplest terms:
Normalization is the process of organizing data in a database. The target is to eliminate redundant data (for example, storing the same data in more than one table) and ensure sensible data dependencies in other words, only storing related data in a table.

First Normal Form (1NF)
First normal form (1NF) is the basic step:
    * Eliminate duplicate columns from the same table (mind it not duplicate rows)
    * Create separate tables for each group of related data and identify each row with a unique column i.e. set of columns (primary key).

Second Normal Form (2NF)
Second normal form (2NF) further removes duplicate data:
    * Should meet the first normal form.
    * Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
    * Create relationships between the new tables and their parents through the use of foreign keys.

Third Normal Form (3NF)
Third normal form (3NF) goes one step further:
    * Should meet the second normal form.
    * Remove columns that are not dependent upon the primary key.

Fourth Normal Form (4NF)
Finally, fourth normal form (4NF) has one additional requirement:
    * Meet the third normal form requirements.
    * There should be no multi-valued dependencies.

Significance is high upto the 3rd normal form. Thereafter the need & existence dries down a little. However we are not to forget the importance of Denormalized databases on OLAP segments. Please refer to MS SQL Server BoK for more details.

Some more benefits of normalization:
a) Optimized queries (because normalized tables produce rapid, efficient joins)
b) Faster index creation and sorting (because the tables have fewer columns)
c) Data integrity (because there is no redundant, neglected data)
d) Faster UPDATE performance (because there are fewer indexes per table)
e) Improved concurrency resolution (because table locks will affect less data)

Database pages simplified..

2011-04-03T04:04:00.000-07:00

Database Pages

A Page is the unit by which SQL Server data is stored. SQL Server 2000 views each data file and breaks it down to 8K byte pages. There are 128 pages stored in 1MB. A page can only belong to one object. The beginning of each page has a header that contains information about the characteristics of that page. The page header takes up the first 96 bytes of each page, leaving 8060 bytes for actual data. The header contains information such as PageID, Previous Page, Next Page, amount of free space available, the object ID of the object that owns the page, etc. By checking the header, SQL Server can quickly determine the status of each page, making it easily identifiable.
To keep track of allocations, SQL Server stores allocation information in certain page types. Lets have a look at them:

1) Global Allocation Map (GAM) Pages

A GAM page reports whether an extent is free or allocated. It has a bit (1 or 0) to keep
track of extent allocations in the interval it covers. A bit set to 1 represents a free extent
and a bit set to 0 represents an extent that is in used. Each GAM page contains information up to about 64,000 extents, which is about 4GB of data. Therefore, a data file could have a GAM page for every 4GB of data. GAM pages reside on page 2 of each data file and appear again every 511,230 pages after the first GAM page.

2) Shared Global Allocation Map pages (SGAM)

SGAM pages record which extents are currently used as mixed extents and have at least
one unused page. A bit set to 1 represents a mixed extent with at least one free page and a bit set to 0 represents an extent which is either not a mixed extent or it is mixed but has no free pages. SGAM pages contain information up to about 64,000 extents, which is about 4GB of data. SGAM pages reside on page 3 of each data file and appear again every 511,230 pages after the first SGAM.
The following shows the bit setting for each extent in the GAM and SGAM pages:
Current Status of an Extent GAM bit setting SGAM bit setting
Free Extent has GAM bit setting of 1 and Mixed extent that has free pages has SGAM setting of 1. Other bits are zero for each.

3) Index Allocation Map (IAM) Page

The IAM pages mark the extents that are allocated to a heap or index, as well as marking
the extents that are allocated to an ntext, text, and image page chain for any table that has
columns of these types. Each heap, index and table with the above data types has a chain of one or more IAM pages to keep track of extents that are allocated to it. The number of IAM pages for an object depends on whether the number of extents exceeds the range that an IAM can record, or if there are multiple data files that host the allocated extents for that object. An IAM page is needed for every data file that hosts extents for that object. IAM
pages are allocated as needed and are located randomly in the data file.

4) Page Free Space (PFS)

PFS pages indicate whether any pages in a Heap or an ntext, text, or image column are
allocated. They also report the amount of free space on each page. Every PFS page covers around 8,000 pages and the next one appears about 8,000 pages after the first page. For every page, PFS has a bitmap that tracks the following space information:
a) Empty page
b) 1-50% Empty
c) 1-20% Empty
d) 1-5% Empty
e) 0-4% Empty
Once an extent is allocated to an object, SQL Server uses the PFS pages to keep track of
free and used pages. PFS pages are the first page in the data file after the file header page.

Orphaned Distributed Transaction SPID

2011-04-01T01:08:00.002-07:00

Explanation:
This example is of an Orphaned Distributed Transaction SPID which specifically involves MSDTS, the Microsoft Distributed Transaction Coordinator. The is what happens when a transaction involves data which resides on more than one server (such as when a database record is replicated out to two or more servers where MSDTC needs to become involved) and the following happens: a) A server drops off the network
b) There’s a power outage at the distributor server.

The Negative SPID (-2) in SQL Server

Phenomenon: May be you have been investigating a performace issue in SQL Server or exploring the error log file for sometime. But to add to your astonishment you notice that there is a negative SPID = -2 at the top of the chain and is the cause of all deadlocks.

What next?

Definitely you would like to wait (not sure how long?) or try to kill the process.

Here is what you have when you try any of them:

Wait: No use waiting as the process will not complete/refresh untill a server restart.

Kill: Yes. Ofcourse you can try to kill the process by executing KILL -2. Here is your prize:

Msg 6101, Level 16, State 1, Line 1Process ID -2 is not a valid process ID. Choose a number between 1 and 2048

Anything else in Pandora's box?

No...?

Solution:

Try this.

SELECT req_transactionUOWFROM master..syslockinfo
WHERE req_spid = -2

This will return a 32 digit UOW number which looks like a GUID. Something like ‘BCD12078-0199-1212-B810-AB46A24F2498’
Run the KILL command with the above UOW.

KILL ‘BCD12078-0199-1212-B810-AB46A24F2498’

Now run sp_who2/sp_who. To your surprise its SPID -2 is GONE.

SQL ResultSet in CSV Format

2011-04-01T01:08:00.000-07:00

There might be a requirement very often to dump the query results straightaway into CSV file format or in other words into a .CSV file. Here is what we need to do to achieve the same.

1) In case we are using OSQL through JCL to call the stored proc, then we need to add a new parameter (-s) as "," or ";" to indicate the character separator. E.g. SET PARM5= -s %";"%

The other parameter (-o) needs to be modified to specify (.CSV) as output file format instead of (.TXT).

2) This is not all. The stored proc needs to be modified as well to accommodate the changes. Please refer to these different approaches taken.

SELECTED APPROACH:

a) SELECT 'col_1' + ',' + 'col_2' + ',' + ' '

UNION ALL

SELECT CONVERT (varchar, col_1) + ','
+ CONVERT (varchar, col_2) + ',' + ' '

ALTERNATIVE APPROACH:

a) SELECT 'col_1', ',' , 'col_2'

UNION ALL

SELECT CONVERT (varchar, col_1), ','
, CONVERT (varchar, col_2).................cont.

b) SELECT CONVERT( varchar,col_1) AS [col_1,]
,CONVERT( varchar,col_2) AS [col_2,]........cont.

NOTE:

Conversion to varchar is required to append the character separator.

ISSUES WITH ALTERNATIVE APPROACH:

1) Alignment problem with alphanumeric columns.

2) Space issues which could not be handled through generic sql trim functions.

3) Unoptimized file size.

4) Truncation of data in some cases if varchar size is not carefully specified.

5) Last column of unusual width.

UNSOLVED ISSUES:

1) The column header being present in the final CSV output file.

2) The date field losing its formatibility once converted to varchar and exported to excel.

Unresolved Issue # 1 can be resolved by the following approaches.

The SQL output contains 2 records, the Header name in the first line and the Line (-) in the second line. This can be removed by

1. We can create DTS package to export the data into Text file and call that DTS from the JCL script. This removes the header lines.

2. These lines can be skipped directly from the JCL script itself with the following code

We just need to provide the record length like [LRECL=357]

//SORTCPY EXEC PGM=SORT

//SORTIN DD DSN=&&UCMDFILE, DISP=OLD

//SORTOF01 DD DSN=&&UCMDSORT,

// DISP= (, PASS, DELETE),

// DCB= (RECFM=FB, LRECL=357),

// SPACE= (2048, (500,500), RLSE)

//SYSOUT DD SYSOUT=*

//SYSIN DD *

SORT FIELDS=COPY

OUTFIL FILES=01, STARTREC=2

Table Variable OR Temporary Table

2011-04-01T01:01:00.000-07:00

A few considerations:

Table variables will perform poorly with large record sets, especially when doing joins because there can be no indexes other than a primary key. Beware, though, when many users start using table variables -- large amounts of RAM are used because all temporary variables are stored and processed directly in memory. Table variables should hold no more than 2 Megs to 3 Megs of data each (depending on user load and system memory).

There are some more limitations to the usage to be kept in mind:

• Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
• The table definition cannot be changed after the initial DECLARE statement.
• Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
• CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.