Db/2 LUW – Loading SQL Server / WorldWideImporters

Background

Unfortunately DB/2 LUW Sample database is very small.

Let us copy data from SQL Server’s sample database, WideWorldImportersDW, over to our DB/2 instance.

Outline

  1. In SQL Server
    • Retrieve Data
  2. In DB2/LUW
    • Create database
    • Create Schema
    • Create Tables
    • Remove existing data
    • Load new data

SQL Server

Retrieve Data

Outline

Using SQL Server’s BCP retrieve data from SQL Server.

Dimension.Date

Code


setlocal

set "_sqlInstance=localhost"

set "_database=WideWorldImportersDW"

set "_columnList=*"

set "_datafileFolder=datafile"

set "_option= -c -T -t"," "

if not exist %_datafileFolder% mkdir %_datafileFolder%

bcp "select %_columnList% from [%_database%].[dimension].[date]" queryout %_datafileFolder%\dimension.date.txt  -S %_sqlInstance% %_option%

endlocal

Db/2 LUW

Database

Create

We provided a sample script for creating a new DB/2 LUW database here.

Schema

Create Schema

Dimension

Code

 CREATE SCHEMA "Dimension"

Table

Create Table

Dimension.Date

Code

DROP TABLE "Dimension"."Date"
//

CREATE TABLE "Dimension"."Date"
(
    "Date" date NOT NULL,
    DayNumber int NOT NULL,
    "DAY" varchar(10) NOT NULL,
    "MONTH" varchar(10) NOT NULL,
    ShortMonth varchar(3) NOT NULL,
    CalendarMonthNumber int NOT NULL,
    CalendarMonthLabel varchar(20) NOT NULL,
    CalendarYear int NOT NULL,
    CalendarYearLabel varchar(10) NOT NULL,
    FiscalMonthNumber int NOT NULL,
    FiscalMonthLabel varchar(20) NOT NULL,
    FiscalYear int NOT NULL,
    FiscalYearLabel varchar(10) NOT NULL,
    ISOWeekNumber int NOT NULL

)
IN TS_WIDEWRLD_REG04
//

ALTER TABLE "Dimension"."Date"
      ADD CONSTRAINT "PK_Dimension_Date"
      PRIMARY KEY
      (
        "Date"
      )
//        

Load data into DB/2

Outline

Using Db2 Import utility let us copy our comma delimited file into DB/2 LUW.

Dimension.Date

Code


setlocal

set "_datafile=..\bcp\datafile"

db2 connect to WideWrld

set "_table=\"Dimension\".\"Date\""

rem db2 import from /dev/null of del replace into  %_table% 

db2 "truncate table %_table% reuse storage ignore delete triggers immediate"

db2 commit

db2 import from %_datafile%\dimension.date.txt OF DEL modified by coldel, insert into %_table%

db2 CONNECT RESET

endlocal
Output

dm2Import_20180918_0405PM

AWS/RDS – Monitoring

Background

Wanted to take a quick look at our AWS/RDS SQL Server Instance.

The look will be solely focused on Performance Metrics.

Performance

Outline

  1. CloudWatch
  2. Enhanced Monitoring
  3. OS Process List

CloudWatch

Images

Image – CloudWatch – CPU utilization & DB Connections

cloudWatch_01_20180917_0240PM.PNG

Image – CloudWatch – Free Storage Space, Free-able Memory, IOPs

cloudWatch_02_20180917_0244PM.PNG

Explanation

CPU Utilization

  1. CPU Utilization is around 2 to 3 percentile

DB Connections

  1. 20 to 30 concurrent DB Connections

Free Storage Space  (MB )

  1. Free Storage is 200,000 MB
  2. Or 200 GB

Freeable Memory  (MB )

  1. Freeable Memory is 200 MB
Code
sys.dm_os_sys_memory – Code

 select
		  tblOSMem.total_physical_memory_kb
			 as [physicalMemKB]

		, ( tblOSMem.total_physical_memory_kb )
				/
		  (1000 * 1000)
			 as [physicalMemGB]

	    , tblOSMem.[available_physical_memory_kb]
			 as [availableMemKB]

		, ( tblOSMem.available_physical_memory_kb )
				/
		  (1000)
			 as [avialableMemMB]

		, tblOSMem.[system_memory_state_desc]

		--, tblOSMem.*

from   sys.dm_os_sys_memory  tblOSMem

 

sys.dm_os_sys_memory – Image

sys.dm_os_sys_memory_20180917_0345PM

 

Enhanced Monitoring

Images

Image – Enhanced – Memory

enhancedMonitoring_01_20180917_0247PM.PNG

Image – Enhanced – CPU, Available Disk Space, Total Disk Space

enhancedMonitoring_02_20180917_0247PM.PNG

 

OS Process List

Images

Image – Process List

OSMonitoring_ProcessList_20180917_0236PM.PNG

Explanation

  1. OS Processes :- 1.4 GB
  2. RDS Processes
    • SQLAgent.exe
    • fdlauncher.exe
    • sqlserver.exe

 

 

SQL Server Backup & Performance Counters

Background

Experiencing  longer than expected Production to Development environment backup and restore cycles.

Wanted to capture performance metrics.

Outline

Here are metrics we will consider:

  1. Ongoing Monitoring
    • MS Windows Performance Monitor ( perfmon )
  2. Summary
    • SQL Server Statement

Performance Monitor ( perfmon )

Metrics

Items

  1. SQL Server / Backup Device
    • Device Throughput Bytes/sec
  2. SQL Server, Databases
    • Backup/Restore Throughput/sec

Tabulate

Object Counter
SQL Server, Backup Device Object
Device Throughput Bytes/sec Throughput of read and write operations (in bytes per second) for a backup device used when backing up or restoring databases. This counter exists only while the backup or restore operation is executing.
SQL Server, Databases Object
Backup/Restore Throughput/sec Read/write throughput for backup and restore operations of a database per second. For example, you can measure how the performance of the database backup operation changes when more backup devices are used in parallel or when faster devices are used. Throughput of a database backup or restore operation allows you to determine the progress and performance of your backup and restore operations.

Select Metrics

Selections

Selection – SQLServer:Databases

performanceCounter.choose.SQLServer.Databases.20180914.1202PM.PNG

Selection – SQLServer:Backup Device

performanceCounter.choose.backupDevice.20180914.1045AM.PNG

Captured Metrics

Captured Metrics

Image – 01

performanceCounter.20180914.1137AM.PNG

Image – 02

performanceCounter.20180914.1130AM.PNG

Image – 03

performanceCounter.20180914.1128AM.PNG

Backup Statement Output

Metrics

Output #1

backupDB.20180914.1215PM

 

Summary

  1. Physical Disk/Disk Writes/sec divided ( / ) by Physical Disk/Avg. Disk Bytes/sec
    • Disk Writes/sec
  2. SQLServer:databases Backup/Resource throughput/sec Versus Physical Disk/Disk Writes/sec
    • Likely SQLServer:databases Backup/resource Throughput/sec will be a multiplier of the Physical Disk/Disk Writes/sec.

 

SQLQueryStress – Parameter Substitution

Background

A quick follow-up to our last post on SQLQueryStress.

BTW, that post is here.

Parameter Substitution

Query

Original Query

Code

insert into [dbo].[activity]
default values

Revised Query

Code

select
          tblP.BusinessEntityID
        , tblP.FirstName
        , tblP.LastName
        , tblP.MiddleName

from  [Person].[Person] tblP

where
        (
                ( tblP.LastName = @lname )
            and ( tblP.FirstName = @fname )
        )

Code – Explanation

In the query above, we have two arguments @lname and @fname.

Substitution

Outline
  1. Click the “Parameter Substitution” button
  2. In the “Parameter Substitution” window
    • Click the “Database” button to set the Database that the parameter arguments reside on
    • In the “Parameter Query” textbox, enter a query that will fetch the parameter arguments
    • Click the “Get Columns” button to have the Database return the list of columns that will be returned from running the Query
    • Match each parameter to its corresponding database column
Images
Image – Parameter Substitution

Click on the “Parameter Substitution” button.

parameterSubstitution__Initiate__20180821_1126AM
Image – Parameter Query

Enter the query that will fetch values for all the data-set that will feed our parameters.

parameterSubstitution__ParameterQuery_20180821_1102AM.PNG

Image – Get Columns – Get Columns

Click on the “Get Columns” button.

Clicking on the “Get Columns” button generates the column names from the Parameter Query.

parameterSubstitution__GetColumns_Before_20180821_1104AM.PNG

Image – Get Columns – Align

Map parameters with column names.

parameterSubstitution__GetColumns_After_20180821_1105AM.PNG

 

Load

SQL Profiler

Images

Image 01

payload_20180821_1210PM_01.PNG

Image 02

payload_20180821_1211PM_02.PNG

Explanation

We can observe variability in the RPC:Completed Event captured for each iteration.

SQLQueryStress

Background

Performance Issues & Load Stressing Exercises are never faraway ideas from a DBA thoughts.

Let us try out SQLQueryStress.

 

SQLQueryStress

Lineage

SQLQueryStress born out of work by Adam Machanic.

Now maintained by Erik Ejlskov Jensen ( ErikEJ ).

 

Artifacts

The source code for SQLQueryStress is available from ErikEJ’s GitHub site.

Here is the URL :-

ErikEJ/SqlQueryStress
Link

Download

Please download the source code from the GitHub site:

download_20180820_1107PM

Build

Please launch Visual Studio.

The community edition V2017 is the one that I have.

Access the Source Code’s solution file and  build the solution.

 

Deliverable

Once built we will access the bin\Debug for the executable ( SQLQueryStress.exe )

build_debug_20180820_1114PM

 

Usage

Outline

  1. Please click the Database button to enter Database specification details
    • Server
    • Authentication
      • Integrated Authentication
      • SQL Server Authentication
    • Database
  2. Load Details
    • Number of Iterations
    • Number of Threads

Database

database_20180820_1120PM

Entry

Load Settings

entry_20180821_1232AM

SQL Query

SQLQueryStress_Usage_Perform_20180821_0836AM

Execute Test

Please press GO to execute the test.

 

Review Results

SQLQueryStress_Query0N_Completed_Exceptions_20180821_0907PM.PNG

BitLocker – Configuration – Error – “Unable to find the Reporting Services instance name”

Background

Recently we ran into an “ha ha” moment installing Microsoft BitLocker.

 

BitLocker Administration and Monitoring

Configuring Reports

Here is the “Configuring Reports” window.

ConfigureReports_20180720_1008AM

Error

Error Image

unableToFindTheReportingServicesInstanceNameMSSQLServer

Error Text

Unable to find the Reporting Services instance name <server-name>\MSSQLServer

Trouble Shooting

Reporting Services Configuration Tool ( RSConfigTool )

Launched SQL Server Reporting Services Configuration Tool ( RSConfigTool).

RSConfig_ReportServerStatus_20180720_1033AM.png

Ensured that the service is running and noted the Instance ID.

The Instance ID is SSRS.

Remediation

BitLocker Administration and Monitoring

Configuring Reports

For the instance name, please note the instance name recorded earlier.

That instance name is SSRS.

Configuration SSRSDB.20180720_1041AM

 

 

Stack-overflow Database ( Years 2008 through 2010 )

Background

As always looking for sample code and databases.

This time it is a sample database.

Occasionally, new users ask a Database Administrator for learning tools and materials.

 

Stack Exchange

Stack Exchange, the parent company for Stack Overflow, thankfully avails their database has XML files.

Brent Ozar and Co avails the XML files as Microsoft SQL Server Data & log files.

 

Download

Read about downloading the 10 GB data which covers years 2008 thru 2010 from here.

The actual file is  here. And, it is packaged as a 1 GB compressed file.

 

Extract

Extract using 7-Zip.

 

Attach to SQL Server Instance

Launch SSMS and attach data and log file using something like this.

SQL


USE [master]
GO

CREATE DATABASE [StackOverflow2010]
ON
(
	FILENAME = N'C:\Microsoft\SQLServer\Datafiles\StackExchange\Stackoverflow\Stackoverflow.2010\StackOverflow2010.mdf'
)
LOG ON
(
	FILENAME = N'C:\Microsoft\SQLServer\Logfiles\StackExchange\Stackoverflow\Stackoverflow2010\StackOverflow2010_log.ldf'
)

FOR ATTACH

GO

 

Output

 

Converting database 'StackOverflow2010' from version 655 to the current version 869.
Database 'StackOverflow2010' running the upgrade step from version 655 to version 668.
...
...
Database 'StackOverflow2010' running the upgrade step from version 866 to version 867.
Database 'StackOverflow2010' running the upgrade step from version 867 to version 868.
Database 'StackOverflow2010' running the upgrade step from version 868 to version 869. 

Explanation

The version of the packaged database is v2008.

When attached to a later model SQL Server, it is upgraded to that later version.

That explains the output entries “upgrade step from version [m] to version [n].

 

Create Indexes

Based on your query needs please create indexes

Here are targeted indexes for our lone test query

Table – [dbo].[Votes]

Index – [dbo].[Votes].[INDX_UserId_BountyAmount]


use [StackOverflow2010]
go

create index [INDX_UserId_BountyAmount]
on [dbo].[Votes]
(
	  [UserId] asc
	, [BountyAmount] desc
)
with
(
	  FillFactor=100
	, DROP_EXISTING=OFF
)

 

Sample Queries

Query – Users with highest bounties

Query


set transaction isolation level read uncommitted
go

use [StackOverflow2010]
go

; with [cteBountyAmount]
as
(
	select top 15
			  tblV.[UserId]
			, [BountyAmount]
				= sum(tblV.[BountyAmount])

	from [dbo].[Votes] tblV

	group by
		tblV.[UserId]

	order by
		sum(tblV.[BountyAmount]) desc

)

select
		  [UserId]
			= tblU.[Id]

		, [name]
			= tblU.DisplayName

		, [url]
			= tblU.WebsiteUrl

		, [bountyAmount]
			= cteBA.[BountyAmount]

from   [dbo].[Users] tblU

inner join [cteBountyAmount] cteBA

	on tblU.[Id] = cteBA.UserId

order by
	cteBA.[BountyAmount] desc

 

Output