Microsoft

Db/2 LUW – Loading SQL Server / WorldWideImporters

Daniel Adeniji bcp, DB/2, DB/2 - Products, DB/2 - Products - LUW, Microsoft, SQL Server, Technical

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

Daniel Adeniji Amazon, Amazon CloudWatch, Amazon Web Services ( AWS ), Microsoft, Relational Data Services ( RDS ), SQL Server, sys.dm_os_sys_memory, Technical

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

Daniel Adeniji Backup, Database Backup\Restore, Microsoft, PerfMon, perfmon, SQL Server, Technical

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.

 

Finding Binaries

Daniel Adeniji Linux, Linux Commands, locate, Microsoft, MS Windows, Technical, which

Background

Trying to explore the various avenues for finding binaries in Linux & MS Windows.

Linux

Outline

  1. locate
  2. which

Command

locate

Outline

locate reads one or more databases prepared by updatedb and writes
file names matching to standard output, one per line.

By default, locate does not check whether files found in database still
exist. locate can never report files created after the most recent
update of the relevant database.

 

Syntax
locate [command]
Syntax

locate ilist
Output

linux_locate_20180912_0402PM

which

Outline

Which takes one or more arguments. For each of its arguments it prints
to stdout the full path of the executables that would have been exe-
cuted when this argument had been entered at the shell prompt. It does
this by searching for an executable or script in the directories listed
in the environment variable PATH using the same algorithm as bash.

Syntax

which [command]
Sample

which ilist
Output

linux_which_20180912_0254PM.PNG

 

MS Windows

Outline

  1. where

Command

where

Outline

Displays the location of files that match the search pattern.
By default, the search is done along the current directory and in the paths specified by the PATH environment variable.

Syntax

where [command]
Sample

where ilist
Output

where_20180912_0411PM

Win OS – Error – “Replication of license information failed because the License Logging Service on server could not be contacted”

Daniel Adeniji Microsoft, Technical, Win OS , ,

Background

Reviewing MS Windows Event Viewer and noticed a recurring error “Replication of license information failed because the License Logging Service on server [server-name] could not be contacted“.

Error

Event Viewer

Event Properties

Here is the error message…

Image

eventProperties_20180904_0119AM.png

Textual

  1. Source :- LicenseService
  2. Event ID :- 213

 

Events

Image

eventViewer_20180904_0119AM.png

Troubleshooting

Logging Service

Googled on the error message and directed to check the “Logging Service” on the remote computer.

Computer Manager

Launched “Computer Manager” and connected to remote computer.

Services

Service – License Logging

Outline
  1. Changed Service Mode from disabled to Automatic
  2. Start Service
Original – Service – Disabled
Image

licenseLogging_Disabled_20180904_0121AM.png

 

Service – Startup Type – Changed to Automatic
Image

licenseLogging_Automatic_20180904_0122AM.png

Service – Service Started
Image

licenseLogging_Automatic_Started_20180904_0123AM.png

Win OS – Run Application has an Administrator

Daniel Adeniji IBM Data Studio, Microsoft, Run as an Administrator ( Win OS / Tasks ), Sarah Jacobsson Purewal, Technical, Win OS

Background

In a previous post, “Data Studio – Error – ‘configuration\org.eclipse.osgi.manager.fileTableLock (Access is denied.)’ ”, spoke of the need to run an application as an Administrator.

That post is here.

Always “Run As An Administrator“

Let us see what it will take to always run an Application as an Administrator.

Guide

Googled on our title and found a very worthy post.

It is by “Sarah Jacobsson Purewal” and it is titled “Always run a program in administrator mode in Windows 10” as our guide.

That post is here.

Steps

Outline

  1. Identify Application Launcher
  2. Review Application Launcher
    • Review Application’s folder and binary name
  3. Change Application’s compatibility mode
    • Change “Run as an Administrator” mode for all users

Identify Application Launcher

Search for Application

In Windows Desktop, please search for the Application.

In our case “IBM Data Studio“.

desktop_datastudio_properties_20180830_0848AM.PNG

Open file Location

Please right click on the app and the drop-down menu appears.

From the drop-down menu, please choose “Open File Location“.

desktop_datastudio_openfilelocation_20180830_0853AM

Review Application Launcher

Start Menu \ Programs \ [Application]

Here is what the start menu for our application looks like.

startmenu_programs_ibmdatastudio_20180830_0903AM.PNG

Start Menu \ Programs \ [Application] \ [shortcut]

Let us access the short cut that we want to dig into.

Data Studio 4.1.3.3 Client
Properties

We selected the shortcut and right clicked on it.

Here is what our drop-down looks like.

startmenu_programs_ibmdatastudio_properties_dropdown_20180830_0910AM

Shortcut – Image

startmenu_datastudio_properties_shortcut_20180830_0914AM.PNG

Shortcut -Textual
  1. Target :- “E:\Program Files\IBM\DS4.1.3\eclipse.exe” -product com.ibm.datastudio.consolidated.product.ide
  2. Start In :- “E:\Program Files\IBM\DS4.1.3”

 

Application’s compatibility mode

Our application’s full name is “E:\Program Files\IBM\DS4.1.3\eclipse.exe”.

Let us go set its compatibility mode.

Windows Explorer

Using Windows Explorer, we accessed our application’s folder, E:\Program Files\IBM\DS4.1.3\.

windowsExplorer_IBMDataStudio_20180830_0926AM

 

Windows Explorer – Application

We selected our application and right clicked on it.

windowsExplorer_IBMDataStudio_properties_20180830_0928AM

Properties

Properties – Tab – General

Here is the initial tab when the Properties for an Application is accessed.

Properties_eclipse.exe_tab_general_20180830.0934AM

Properties – Tab – Compatibility

Please access the Compatibility tab.

Properties_eclipse.exe_tab_compatibility_01_20180830.0937AM

Please click on the “Change Setting for all users” button.

Properties – Tab – Compatibility – Change Settings for all users

Here is what the “Change Settings for all users” window look like.

Properties_eclipse.exe_tab_compatibility_changeSettigforallusers_01_20180830.0941AM

Please make the changes identified below:

  1. Run this program as an Administrator

Here is a screen that reflects our change.

eclipse.exe.compatibilityforallusers.20180830.0945AM

 

Dedication

Dedicated to Sarah Jacobsson Purewal.

SQLQueryStress – Parameter Substitution

Daniel Adeniji Adam Machanic, Erik Ejlskov Jensen ( ErikEJ ), Microsoft, SQL Server, SQL Server Profiler, SQLQueryStress, Trace File ( SQL Server Profiler )

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.