Sunday, June 30, 2013

0 Not enough log space for roll back transaction in tempdb

By following Bob ward’s “Inside tempdb” session in the SQL SUMMIT 2011. I want to try to reproduce the tempdb log full and cause the SQL Server shutdown.scenario. 

My test version of SQL Server is 2008R2.

Here are the steps to reproduce the error

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE =1034KB,maxsize=1024KB, FILEGROWTH = 0 )
GO
sp_helpdb tempdb

image

0 Could not obtain exclusive lock on database 'model'. Retry the operation later.

Today, I was testing something in the SQL Server and while trying to create the database, I got below error.

 

Msg 1807, Level 16, State 3, Line 2
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Msg 1802, Level 16, State 4, Line 2
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

 

We all know that the model database is used for creating the database. If you want to have some objects being part of new database, we can put in the model. During the database creation, sql serve would copy the model and create the new one and then run the alter database to make to the size you specify in the create database syntax.

However, it is new to me that the create database statement require hold the exclusive lock on the model db, it does make sense so because we don’t want other processes is changing the model database while we are creating the new database.

 

To reproduce this error, just open any process and issue ‘use model’ and in another session , run the create database statement.

In below screenshot, the first top output is from sp_who2, you can see, spid 54 is in mode database and it is blocking the spid 54 ‘Create database’.

The 2nd part of output is from sys.dm_os_waiting_tasks, the spid 60 is waiting for the LCK_M_X . LCK_M_X is the exclusive locks .

image

0 Sqldumper and winDbg

Sqldummper.exe is the SQL Server utility that can take the memory dump for the SQL server. After taking the memory dump, we can load it using the winDbg to see the internal call for trouble shooting.

Sqldummper comes with the sql server install and you can find it in the common shared folder.

SQLServerInstallDrive:\Program Files\Microsoft SQL Server\number\Shared

Note In this folder path, number is a placeholder for one of the following:

  • The folder name is 110 for SQL Server 2012.
  • The folder name is 100 for SQL Server 2008.
  • The folder name 90 for SQL Server 2005.

Here is the example to take the mini memory dump and put the dump file in the C:\temp

image

Friday, June 28, 2013

1 Build Fake NUMA test environment with BCDEdit

My last attempt to create the NUMA lab environment with Aamazon EC2 was successful . ( see pot here). However, it will cost 7.25 per hr.

During the research of NUMA, I found the article by Jonathan Kehayias. He use BCDEdit to fake the hardware NUMA. This seems very interesting so I decide to give it a try.

My VM environment is Virtual Box, I was not sure it can work because it does not let me specify number of core and cpu like VMWare does.

image

If the VM is VMware workstation, it let us specify the number of core and CPU.

image

0 Running Microsoft SQL Server on AWS EC2

In my last post, I create my MSSQL instance on the AWS RDS. This time, I am going to try hosting the SQL Server on the EC2. One of the major difference between EC2 and RDS is, in RDS, we only get the instance. In EC2, We have access the Host.

image

Thursday, June 27, 2013

0 Running Microsoft SQL Server on AWS RDS

Amazon provide the platform which allow user to set up the SQL Server very quickly …. Today, I decided to give it a try and feel it is too easy to be true .

First, you have to have the AWS account being created.

image

Tuesday, June 25, 2013

0 Using cursor to load data across schema/database

 

Use cursor to move data between tables

db2 "DECLARE MYCURS1 CURSOR FOR SELECT * FROM DEPARTMENT"
 
db2 LOAD FROM MYCURS1 OF CURSOR INSERT INTO DB2DEMO.DEPARTMENT

image

Use cursors to move data between database

 

db2 "DECLARE MYCURS2 CURSOR DATABASE sample10 user db2inst1 using XXXXX FOR SELECT * FROM DEPARTMENT"
 
db2 LOAD FROM MYCURS2 OF CURSOR INSERT INTO DB2DEMO.DEPARTMENT

image

Reference

http://www.ibm.com/developerworks/data/library/techarticle/dm-0901fechner/

1 DB2 Export/Import/load

Export

DB2 export utility can export the table data to 4 different file format:

  • DEL : delimited ASCII format
  • WSF: work sheet format
  • IXF: Integration Exchange Format, PC version. Binary format

Below example is to export the file as binary format.

 db2 export to /tmp/department.ixf of ixf messages /tmp/export_department.log "select * from department"

image

The SQL statement must be in quote, otherwise, it will get the “SQL3022N  An SQL error "-104" occurred

image

Below example is to export the file as DEL format , by default it is separate by common separate .

db2 export to /tmp/department.del of del messages /tmp/export_del_department.log "select * from department"

image

image

Import

  • Only the binary format of the data ( IXF) can use CREATE INTO  and REPLACE_CREATE to create the table during the import time. However, if the sourcing table has index , the index may create fail because the import would try to create it with the same name as source table.
  • REPLACE: this would delete all existing data before insert it.

 

Below example is to import the IXF data and create the table.

 db2 import from /tmp/department.ixf of ixf messages /tmp/import_ixf_department.log "create into department2"

image

The data looks good but index does not get created and the error is showing in the message file.

image

image

Below example is to import the del data with REPLACE

  db2 import from /tmp/department.del of del messages /tmp/import_del_department.log "replace into department2"

image

Load

  • Another utility for loading data is using load command or db2load API.
  • The load utility is faster than the import utility because it writes formatted pages directly to the database not running the individual insert as import .
  • The load operation maintains:
    • unique constrains.
    • Range constrains for partition tables
    • generated columns
    • LBAC security rules.
  • For other constrains, the table is placed in the SET INTERGRITY PENDING at beginning of the load. After the load, the SET INTERGRITY must be used to take the table out of pending state.
  • If the db2 server and the load directory is not on the same host, add the client keyword after the load : ex  db2 load client ….

 

Below example is to load IXF data and replace existing data.

db2 load from /tmp/department.ixf of ixf messages /tmp/load_ixf_department.log replace into department2

image

Below example is to load IXF data and insert into the table.

db2 load from /tmp/department.ixf of ixf messages /tmp/load_ixf_department.log insert  into department2

image

Below example is to load DEL data and insert into the table.

 db2 load from /tmp/department.del of del messages /tmp/load_del_department.log insert  into department2

image

Another character is the load can replace existing data even there is a dependence for other table. Import can not replace the existing data if other tables has dependence on it.

-- Below would work
db2 load from /tmp/department.ixf of ixf messages /tmp/load_ixf_department.log replace into department
 
db2 “select tabname,status from syscat.tables where tabname='DEPARTMENT' and tabschema='DB2INST1'”
 
db2 “SET INTEGRITY  for DEPARTMENT,EMPLOYEE IMMEDIATE CHECKED”
 
--- Below would fail becuase department table has index, and constraint that is refence by other table
db2 import from /tmp/department.del of del messages /tmp/import_del_department.log "replace into department"

image

The load log shows the index would be rebuild after the load .

image

If the table reach an unstable state due to incomplete loading. We can use below statement to take out the unstable state.

db2 load client from /dev/null of ixf messages /tmp/xxx terminate into department2

 

Reference

Monday, June 24, 2013

0 DB2 Package

Package is the logical grouping for the resource that require to execute the routine . It is also known as “Access plan” .  Rebind package is the same concept as recompile in other DBMS ( MSSQL, Sybase and Oracle).

  • List all the packages
  • Rebind the package
  • Rebind all package
  • PKGCACHE procedure - generate a summary report of package cache metrics

 

List all the packages

 
 
 
 --- ALl packages: db2 list package for all
   select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
      valid, format, isolation, blocking
   from syscat.packages
   order by pkgschema, pkgname, pkgversion;
 
   --- Package own by NULLID schema: db2 list package for system
   select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
      valid, format, isolation, blocking
   from syscat.packages
   where pkgschema = 'NULLID'
   order by pkgschema, pkgname, pkgversion;
 
   -- Packages own by user: db2 list package 
   select pkgname, pkgschema, pkgversion, unique_id, boundby,reoptvar, total_sect,
      valid, format, isolation, blocking
   from syscat.packages
   where pkgschema = USER
   order by pkgschema, pkgname, pkgversion; 

image

image

REOPTVAR:  Indicates whether the access path is determined again at execution time using input variable values.

  • A = Access path is re optimized for every OPEN or EXECUTE request
  • N = Access path is determined at bind time
  • O = Access path is re optimized only at the first OPEN or EXECUTE request; it is subsequently cached

Rebind the package

  • VALID column indicate whether the package still valid and if it is N, that means the package is not valid and needs to be rebind. 
  • If there is the current user session is using the package, the bind would not complete until the user’s unit of the work complete. the bind would need to hold the exclusive lock in the package in the syscat.package table.

 

SELECT PKGNAME, PKGSCHEMA, PKGVERSION, UNIQUE_ID, BOUNDBY, REOPTVAR,
  TOTAL_SECT, CREATE_TIME, EXPLICIT_BIND_TIME, LAST_BIND_TIME,
  VALID, FORMAT, ISOLATION, BLOCKING
  FROM SYSCAT.PACKAGES
  WHERE PKGNAME = 'P5582207';

image

 db2 rebind P5582207 reopt once

 

image

image

Another way to rebind the package is to use SYSPROC.REBIND_ROUTINE_PACKAGE

However, the SYSPROC.REBIND_ROUTINE_PACKAGE will need to use the routine name ( store procedure name ).

 

To Find the routine name from package name we have to join through the SYSCAT.ROUTINEDEP

  SELECT DISTINCT A.ROUTINENAME, B.BNAME FROM SYSCAT.ROUTINES A, 
SYSCAT.ROUTINEDEP B, SYSCAT.PACKAGES C WHERE
B.ROUTINENAME = A.SPECIFICNAME AND B.BNAME = C.PKGNAME AND C.PKGNAME = 'P5582207'

image

CALL SYSPROC.REBIND_ROUTINE_PACKAGE (
  'P','BONUS_INCREASE','RESOLVE ANY REOPT ONCE APREUSE YES')

image

Rebind all package

Below command will rebind the invalid package only.

db2rbind sample  -l /tmp/rebindall.log

image

use all parameter would bind all packages.

db2rbind sample  -l /tmp/rebindall.log all

image

 

PKGCACHE procedure - generate a summary report of package cache metrics

Generate the report for what is in the package cache

db2 "call monreport.pkgcache"

-- Last 30 mins

db2 “call monreport.pkgcache(30)”

image

Reference

0 List Store procedure in DB2/Oracle/MSSQL

 

DB2

syscat.routines has all the sp information including its contents.

List all store procedure in the database

select routineschema, routinename,specificname from syscat.routines where routinetype ='P'

image

The text column has the content of the store procedure.

image

Oracle

PORCEDURES view list all the store procedures in the database.

SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE='PROCEDURE'; 
SELECT * FROM DBA_PROCEDURES  WHERE OBJECT_TYPE='PROCEDURE';
SELECT * FROM ALL_PROCEDURES  WHERE OBJECT_TYPE='PROCEDURE';

image

SOURCE view has the content of the store procedure.

 

SELECT * FROM USER_SOURCE WHERE NAME = 'STORED_PROC'; 
SELECT * FROM DBA_SOURCE WHERE NAME = 'STORED_PROC';
SELECT * FROM ALL_SOURCE WHERE NAME = 'STORED_PROC'; 

image

MSSQL

sys.procedures in each database has all the sp information in this db. To get all SP in all dbs, we can use sp_MSForEachDB .

    EXEC sp_MSForEachDB 'SELECT name   FROM  sys.procedures' 

image

Sys.objects also has the similar information.

select name from sys.objects where type='P';

image

To get detail of the sp, we can use sp_helptext

-- sp_helptext SPNAME
sp_helptext FindItemsByDataSet
image

Sunday, June 23, 2013

0 Find the object definition in DB2/Oracle/MSSQL

 

DB2

 db2 describe table T2

image

Oracle

desc user_tables

image

MSSQL

sp_help objectname

image

Saturday, June 22, 2013

0 DGTT and CGTT

DGCC: Does not appear in the system catalog, which makes them not persistent for use by other application/session. The table would disappear after session is terminate.

CGTT: Does exist in the system catalog. Can share by other application.

Both temporary table are created in the USER temporary table space. NOT in SYSTEM temporary table space.

 

DGTT

To refer the DGTT, we must use SESSION as qualify schema name.

db2 "declare global temporary table DGTT_DEMO1( c1 integer,  c2  varchar(20) )  on commit preserve rows"
 
db2 "insert into SESSION.DGTT_DEMO1 values (1,'row1')"
db2 "insert into SESSION.DGTT_DEMO1 values (2,'row2')"
db2 "select * from SESSION.DGTT_DEMO1"
 
db2 connect reset
 
db2 connect to sample
 
db2 "select * from SESSION.DGTT_DEMO1"

image

If we reset the connection, the table would not be present and get the SQL0204 error when we try to use it.

CGTT

The CGTT exist in the system catalog with the type ‘G’. The table would be preserve but the data would be gone after session reset.

db2 "create global temporary table CGTT_DEMO1( c1 integer,  c2  varchar(20) )  on commit preserve rows"
 
db2 "insert into CGTT_DEMO1 values (1,'row1')"
db2 "insert into CGTT_DEMO1 values (2,'row2')"
db2 "select * from CGTT_DEMO1"
 
db2 connect reset
 
db2 connect to sample
 
db2 "select * from CGTT_DEMO1"
db2 "select tabschema,tabname ,type from syscat.tables  where tabschema=current schema  and tabname = 'CGTT_DEMO1'"

image

Reference

http://www.ibm.com/developerworks/data/library/techarticle/dm-0912globaltemptable/

0 DB2 NOT LOGGED INITIALLY

“NOT LOGGED INITIALLY” is quite interesting feature in DB2. Essentially, it change the behavior of logging for the table.

By default, all the changes will be log within the transaction, however when turn on this feature on the table level. INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE operation on the table would not be log in the same unit of the work.  The primary usage for this is when the T1 table is staging area and can be easily created from other source, therefore to avoid the overhead of the logging during the creation time, we can turn off the logging for the table.

To turn on this feature:

CREATE TABLE T1(C1 INT,C_DESC CHAR(20)) NOT LOGGED INITIALLY;

or

ALTER TABLE T1 ACTIVATE NOT LOGGED INITIALLY;

In Oracle, the alternative is use /*+ APPEND */

INSERT /*+ APPEND */ INTO t select c2,desc2 from t2;

But this would only affect the insert statement and the table would not be accessible until commit.

 

Truncate table

To truncate the table in db2:

ALTER TABLE t1 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;

In other DBMS: Sybase, Oracle and SQL server.

truncate table t1;

 

Reference

Wednesday, June 19, 2013

1 NTLMv2 and FreeTDS

Windows use NTLM authentication for very long time. FreeTDS is very popular open source database driver.  Recently, I spend quite some time to trouble shooting how to use FreeTDS connect to SQL Server which has NTLMv2 enable.

 

How to determine the NTLM version

The NTLM setting can be configure on both Server side and client side. Here are the steps you can check and verify the NTLM setting.

gpedit.msc -> Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> Security Options –>  Network Security: LAN Manager Authentication Level.

image

image

reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa /v LmCompatibilityLevel

image

When set as “Send NTLMv2 response only. Refuse LM&NTLM”, the value reflect on the register key is 5.

Here list all the incompatibility :

  • 0: Clients use LM and NTLM authentication, but they never use NTLMv2 session security. Domain controllers accept LM, NTLM, and NTLMv2 authentication.
  • 1: Clients use LM and NTLM authentication, and they use NTLMv2 session security if the server supports it. Domain controllers accept LM, NTLM, and NTLMv2 authentication.
  • 2:Clients use only NTLM authentication, and they use NTLMv2 session security if the server supports it. Domain controller accepts LM, NTLM, and NTLMv2 authentication.
  • 3:Clients use only NTLMv2 authentication, and they use NTLMv2 session security if the server supports it. Domain controllers accept LM, NTLM, and NTLMv2 authentication.
  • 4:Clients use only NTLMv2 authentication, and they use NTLMv2 session security if the server supports it. Domain controller refuses LM authentication responses, but it accepts NTLM and NTLMv2.
  • 5:Clients use only NTLMv2 authentication, and they use NTLMv2 session security if the server supports it. Domain controller refuses LM and NTLM authentication responses, but it accepts NTLMv2.

Essentially, if the value is set to 5. the Server would only accept the NTLMv2.

FreeTDS support and configuration

The older version of the freeTDS (0.8)  does not support the NTLMv2 and it require the configuration in the newer release ( 0.9.)

Here is the sample of freetds.conf

[DB SERVER NAME]
        host = 127.0.0.1
        port = 8001
        use ntlmv2 = yes

 

The “use ntlmv2=yes” keyword is required for FreeTDS to use NTLMv2. If you use the older version of the FreeTDS, it would simply ignore this value. If the OS does not use NTLMv2, it will fail back to use NTLM.  Therefore I see no downside to set it up in the freetds.conf.

If the OS is set to NTLMv2 (5) but there is no NTLMv2 support turn on on the FreeTDS, we would get the error message like below :

 

Msg 18452 (severity 14, state 1) from DEPOTSQL Line 1: "Login failed. The login is from an untrusted domain and cannot be  used with Windows authentication.

Reference

0 DB2_COMPATIBILITY_VECTOR

The DB2_COMPATIBILITY_VECTOR registry variable is used to enable one or more DB2® compatibility features introduced since DB2 Version 9.5.

This register key is to enable the DB2 SQL compatibility to other product.

There are 3 major product are supported:

  • SYB: for Sybase
  • ORA: for Oracle 10FFF
  • MYS: for MySQL 3004

  We can use db2set to change it and this changes require bounce the db2 instance in order to take effective. The value can be set either by name or the hex value.

Below example is to set the DB2 compatible mode to Oracle , so we can use dual instead of SYSIBM.SYSDUMMY1.

db2set DB2_COMPATIBILITY_VECTOR=ORA
db2stop force
db2start

image

However, the ORA value is not valid in the DB2 Express-C version, if we try to set it to ORA, we would get the DBI1301E  Invalid value error.

image

The work around is to set by individual features, for example, to enable dual dummy table, we have to set it as 2.  Hex value ( 10FFF) is still not supported in Express-C.

image

 

Reference

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates