Importing Multiple Files to MYSQL database

Import multiple files of mysql dump .SQL files: (Example: many dump files containing table structure and data)

This is through Linux Terminal:
cd /glide/mysql/server/bin

ls -1 /glide/tempbackup/dumped_data/*.sql | awk '{ print "source",$0 }' | ./mysql --batch -u username -p databasename

Once data imported, go to MYSQL terminal and see those imported data:

From Terminal: sudo -su mysql
From Terminal: export PATH=/glide/mysql/server/bin:$PATH
From Terminal mysql -u root -p  (This command will prompt you to enter the mysql root password)

Space Check - Linux / Unix

=========
[root@pdb01.in1.dbms.com pgsql]# date;hostname;df -h|grep /apps;du -sh *|grep data
Thu Oct  8 04:24:56 GMT 2015
pdb01.in1.dbms.com
/dev/mapper/vg0-lv_apps   29G   27G  331M  97% /apps
30G     data
=========


Top 20 large files Findings:
====================
find /u02 /type f -printf '%s%p\n' | sort -nr | head -20
|find /u02 /type f -printf '%s%p\n' | sort -nr | head -20 | awk '{print $1/1024/1024/1024 "GB" " $2}'

or

[user@srv-01]$ find /usr/ -xdev -depth -type f -ls |sort -nr -k 7 |head -20 | awk '{print $7,$11}'

More Find commands available from below website too:
========================================
Reference:  http://shannuradba.blogspot.co.uk/2012/07/find-command-with-examples.html

To see the size of the folder:

du -sk *| sort -nr

Examples:

[root@rac1 ~]# cd /
[root@rac1 /]# pwd
/
[root@rac1 /]# du -sk *| sort -nr

Crontab

[username@hostname ~]$uname -a

[username@hostname ~]$whoami

[username@hostname ~]$crontab -l

-- The message of job will display for the user username

[username@hostname ~]$sudo sudo -s

or

[username@hostname ~]$sudo sudo su -

[root@hostname ~]#crontabl -l

-- The message of job will display for the user root


[root@hostname ~]#crontab -l -u oracle

[root@hostname ~]#grep -i ora /etc/passwd
[root@hostname ~]#cat /etc/crontab
[root@hostname ~]#find /etc/crontab


[root@hostname ~]#crontab -l -u oracle

#General

01  23  *  *  4 /u01/oracle/admin/maint/scripts/housekeepingtiday.sh instancename > /u01/oracle/admin/maint/log/housekeepingtidy_InstanceName_$$.log 2>&1

Reference is below:

http://www.adminschoice.com/crontab-quick-reference

Crontab file
Crontab syntax :

A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

*     *   *  *   *  command to be executed -     -    -   -  - |     |     |   |    | |     |     |   |    +----- day of week (0 - 6) (Sunday=0) |     |     |   +------- month (1 - 12) |     |     +--------- day of month (1 - 31) |     +----------- hour (0 - 23) +------------- min (0 - 59)

* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).
Notes
A. ) Repeat pattern like /2 for every 2 minutes or /10 for every 10 minutes is not supported by all operating systems. If you try to use it and crontab complains it is probably not supported.
B.) The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

Oracle Process Status

TNSListener:

ps -ef | grep -i lsnr

or

ps -fu oracle | grep lsnr

To find Oracle Process:
----------------------------

ps -ef | grep -i pmon

or

ps -ef | grep -i smon

-- using ps to find the PID of listener processes
/home/oracle> ps -fu oracle | grep lsnr
oracle    8683  8462  0 12:10 pts/0    00:00:00 grep lsnr
oracle   28793     1  0 Apr26 ?        00:00:28 /software/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle   31744     1  0 Apr26 ?        00:00:49 /software/grid/bin/tnslsnr LISTENER -inherit

-- using lsof to list IP ports (using numerics) opened by the specified PID
/home/oracle> /usr/sbin/lsof  -n -P -p 31744 | grep IP
tnslsnr 31744 oracle   12u  IPv4            2154732                 TCP 127.0.0.1:41209->127.0.0.1:6100 (ESTABLISHED)
tnslsnr 31744 oracle   14u  IPv4            2154769                 TCP 10.251.95.67:1521 (LISTEN)
tnslsnr 31744 oracle   15u  IPv4            2154770                 TCP 10.251.95.80:1521 (LISTEN)
tnslsnr 31744 oracle   16u  IPv4            2155930                 TCP 10.251.95.80:1521->10.251.95.80:44979 (ESTABLISHED)
tnslsnr 31744 oracle   18u  IPv4            5171001                 TCP 10.251.95.80:1521->10.251.95.80:46779 (ESTABLISHED)
oracle@nvs-dev1: /home/oracle>

Find - Oracle DB Shut down Scripts Location

Steps involved for oracle server to shutdown:
--------------------------------------------------------

1- login to database server as  DBAPriviledgedUsername
2- [root@oracledbserver ~]$su - oracle  -- sudo oracle 
3- [root@oracledbserver ~]$.  oraenv  -->Type the Oracle_sid which you want to connect to, that is basically an oracle instance
 (Note: Oracle_sid should be in /etc/oratab)
4- [root@oracledbserver ~]$sqlplus / as  sysdba
5  SQL> select instance_name from v$instance;
or
SQL>select sys_context('USERENV','INSTANCE_NAME') from dual;  -- Please make sure you are in correct db instance

6- Please check  if they are the same instance as per above query where you are trying to start and / or shutdown the instance
7- shutdown immediate -- This will terminate or kill the sessions and rollback the sessions


Locate the dbstart and dbshut scripts:
------------------------------------------

[oracle@oracledbserver admin]$ cd /etc/init.d

[oracle@oracledbserver init.d]$ locate dbshut

/apps/home/oracle/OracleHomes/db10g/bin/dbshut
/apps2/oracle/app/oracle/product/11.2.0/dbhome_1/bin/dbshut
/apps2/oracle/app/oracle/product/12c/bin/dbshut

[oracle@oracledbserver init.d]$ locate dbstart

/apps/home/oracle/OracleHomes/db10g/bin/dbstart
/apps2/db2/samples/cobol_mf/dbstart.cbl
/apps2/oracle/app/oracle/product/11.2.0/dbhome_1/bin/dbstart

/apps2/oracle/app/oracle/product/12c/bin/dbstart

CPU or Load Average is Critical in Linux / Unix

Server Uptime:

--------------------

[username@hostname #]$uptime

Server  Hostname:

----------------------------

[username@hostname #]$hostname

Server  IP Address:

----------------------------

[username@hostname #]$/sbin/ifconfig

or 

[username@hostname #]$sudo /sbin/ifconfig  -- If y[username@hostname #]$/sbin/ifconfig

or 

[username@hostname #]$/sbin/ifconfig | grep 'inet addr:'  -- To get all the IP addresses of that server like eth0, eth1 lo

or

[username@hostname #]$/sbin/ifconfig eth0 | grep 'inet addr:' 

 

Server   mpstat:

------------------------

he mpstat command is used for checking the cpu usage in Linux and /or monitor the Linux system performance. 

The output result gives the processor statistics. 

If your system has multiple processor cores, you can use the mpstat command to monitor each individual core. The mpstat command provides the same CPU utilization statistics asvmstat, but mpstat gives the statistics out on a per processor basis

[username@hostname #]$/usr/bin/mpstat -P ALL

Server   vmstat:

------------------------

[username@hostname #]$vmstat 5 1   --- It execute 1 time only at the interval of 5 seconds

--- Some of the time, r (runqueue) value is higher than processor exists in the server which means CPU bottleneck.

Server Processor exists in the server:

-----------------------------------------------

[username@hostname #]$ grep -c "processor" /proc/cpuinfo

List of users logged into the system:

-----------------------------------------------

[username@hostname #]$sudo w

Top 5 memory hungry processes:

-----------------------------------------------

[username@hostname #]$ps auxxx --sort=-rss | head -6

Linux Services Check

svcs -a

svcs -a | grep online

svcs -a | grep offline

svcs -a | grep disabled

Oracle Instance Running Check

Oracle Instances:
----------------

ps -ef | grep pmon

ps -ef | grep smon

ps -ef | grep ckpt

TSQL Linux Utility

rpm -qa |grep free

/user/bin/tsql –H XXX.XX.XX.XX –p 1433 –U username –P Password -D dbname

nano /etc/freetds.conf --- to edit file

To view the freetds.conf file
-----------------------------

cd /etc
ls
less freetds.conf

Reference:

https://zxtech.wordpress.com/2013/06/22/how-to-connect-to-microsoft-sql-server-from-ubuntu-using-odbc-part-3-of-3/

RPM

rpm -ql unixodbc - To see whether it is installed or not

rpm -qa |grep free or rpm -qa |grep traceroute

How to see the executable Files:
--------------------------------

control C

control R

Then type naming to look for previous commands

type ts and then tab it gives the executable file start with ts

Message appears tsql utility

Linux Network Troubleshooting

Network Troubleshooting:
------------------------

Trace Route:
------------

-bash-3.2$traceroute IPAddress

command not found

-bash-3.2$echo $PATH
/usr/bin:/bin:/usr/local/bin

-bash-3.2$/usr/sbin/traceroute IPAddress

IPConfig:
--------

/usr/sbin/ifconfig -a -- To see the ip address of solaris system


Trace to certain activities
---------------------------

tcpdump -i eth1 host xxx.xx.xxx.xx ---- Try this in source system. It will give the information when we telnet or connect to other server. xxx.xx.xxx.xx is the IP (Example VIP IP of the RAC server) where we are trying to trace. That is the destination IP.


Telnet:
-------
telnet IP 1433

Netcat or nc is a networking utility for debugging and investigating the network:
--------------------------------------------------------------------------------

nc -v -z IP 1433

Routing Table Information

Routing Table
--------------

Route print

route -n -- Linux

netstat -nr --- Solaris

Pivot Multiple Columns

Reference: http://beyondrelational.com/modules/2/blogs/88/Posts/14283/pivoting-on-multiple-columns-sql-server.aspx

Cloudera Installation

Step by step to install Cloudera. The screenshot itself considered as self study to move forward to next step.

PHP Installation in Windows 2012 Server

http://www.microsoft.com/web/platform/phponwindows.aspx

http://www.microsoft.com/web/downloads/platform.aspx

Pagefile.sys and Hiberfil.sys are filling my SSD Drive

The best way to delete hiberfil.sys or disable hibernate:

Hiberfil.sys clear:

•Go to Start menu, type “cmd” open up command prompt
•Type “powercfg.exe -h off” [make sure you are an Administrator]
•ENTER
•Type “exit”
•ENTER

Paging File Clear:

1.Start Registry Editor (Regedt32.exe).
2.Change the data value of the ClearPageFileAtShutdown value in the following registry key to a value of 1:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management
If the value does not exist, add the following value:
Value Name: ClearPageFileAtShutdown
 Value Type: REG_DWORD
 Value: 1
This change does not take effect until you restart the computer.

Reporting Services – Problem - Your browser does not support scripts or has been configured not to allow scripts

Solution
Should you get it as well follow the instruction below:

•go to (from its menu) Tools/Internet Options
•go to Security tab
•select Trusted zone / sites from the list of available zones
•click Sites button
•in new window provide the URL of the Reports Manager (e.g. http://MACHINE_NAME/*); if you are running Reports Manager not on the default port, provide the port number as well

DYNAMIC TABLE CREATION

CREATE TABLE LISTID (ID INT NOT NULL)

GO

INSERT INTO LISTID  VALUES (111)

INSERT INTO LISTID  VALUES (112)

INSERT INTO LISTID  VALUES (113)

INSERT INTO LISTID  VALUES (114)

INSERT INTO LISTID  VALUES (115)

GO

DECLARE @LISTID TABLE (ID INT NOT NULL)

INSERT INTO @LISTID (ID)

SELECT DISTINCT (ID) FROM LISTID

DECLARE @LISTTALE TABLE (NAME VARCHAR(100) NOT NULL)

INSERT INTO @LISTTALE (NAME)

SELECT 'SELECT * FROM LIST' + CAST(ID AS VARCHAR(100))  FROM @LISTID

DECLARE @LISTSTR VARCHAR(8000);

SET @LISTSTR = ''SELECT @LISTSTR =  @LISTSTR + NAME + ' UNION ALL '

FROM @LISTTALE; SELECT SUBSTRING(@LISTSTR , 1, LEN(@LISTSTR)-9)

GO

DYNAMIC TABLE CREATION:

DECLARE @SQLSTRING NVARCHAR(MAX);                          

SET @SQLSTRING = '

DECLARE @LISTID TABLE (ID INT NOT NULL)

INSERT INTO @LISTID (ID)

SELECT DISTINCT (ID) FROM LISTID

DECLARE @LISTTALE TABLE (NAME VARCHAR(100) NOT NULL)

INSERT INTO @LISTTALE (NAME)

SELECT ''SELECT * FROM LIST'' + CAST(ID AS VARCHAR(100))  FROM @LISTID

DECLARE @LISTSTR VARCHAR(8000);

SET @LISTSTR = ''''SELECT @LISTSTR =  @LISTSTR + NAME + '' UNION ALL ''

FROM @LISTTALE; SELECT SUBSTRING(@LISTSTR , 1, LEN(@LISTSTR)-9)'  --- -9 IS TO REMOVE UNION ALL

PRINT @SQLSTRING

EXEC (@SQLSTRING)

Backup Split - Scripts

BACKUP DATABASE DBNAME TO

DISK = 'E:\TEMP\DBNAME_SPLIT1.BAK',

DISK = 'E:\TEMP\DBNAME_SPLIT2.BAK',

DISK = 'E:\TEMP\DBNAME_SPLIT3.BAK'

,DISK = 'E:\TEMP\DBNAME_SPLIT4.BAK'

,DISK = 'E:\TEMP\DBNAME_SPLIT5.BAK'

,DISK = 'E:\TEMP\DBNAME_SPLIT6.BAK'

,DISK = 'E:\TEMP\DBNAME_SPLIT7.BAK'

,DISK = 'E:\TEMP\DBNAME_SPLIT8.BAK'

,DISK = 'E:\TEMP\DBNAME_SPLIT9.BAK'

,DISK = 'E:\TEMP\DBNAME_SPLIT10.BAK'

WITH COMPRESSION, INIT, STATS=1

GO

GEOLOCATIONS CODE

REFERENCE:

http://www.geodatasource.com/developers/mssql

create function dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)

returns float

as

begin

declare @DegToRad as float

declare @Ans as float

declare @Miles as float

set @DegToRad = 57.29577951

set @Ans = 0

set @Miles = 0

if @lat1 is null or @lat1 = 0 or @long1 is null or @long1 = 0 or @lat2 is

null or @lat2 = 0 or @long2 is null or @long2 = 0

begin

return ( @Miles )

end

set @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)

set @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)

set @Miles = CEILING(@Miles)

return ( @Miles )

end

Reference:

http://www.mssqltips.com/sqlservertip/2690/calculate-the-geographical-distance-between-two-cities-in-sql-server/

TO GET THE MILES AND KILOMETRES:

CREATE PROCEDURE DBO.GEOLOCATIONDISTANCE (@CUSTOMERINDEXKEY_ID INT,@unit varchar(5)) 

AS 

BEGIN 

DECLARE @POSTCODE [VARCHAR](9), @LATITUDE1 [DECIMAL](18,15),@LONGITUDE1 [DECIMAL](18,15) 

SELECT TOP 1 @POSTCODE = REPLACE(POSTCODE,' ','') 

FROM ADDRESS(NOLOCK) WHERE CUSTOMERINDEXKEY_ID = @CUSTOMERINDEXKEY_ID 

ORDER BY SEQNUM DESC 

SELECT @CUSTOMERINDEXKEY_ID AS CUSTOMERINDEXKEY_ID, @POSTCODE AS POSTCODE 

 

SELECT @LATITUDE1 = LATITUDE, @LONGITUDE1 = LONGITUDE FROM PRDDOL.POSTCODELATLNG (NOLOCK) WHERE POSTCODE = @POSTCODE 

 

SELECT @LATITUDE1,@LONGITUDE1 

DECLARE @orig_lat DECIMAL(12,9) 

DECLARE @orig_lng DECIMAL(12,9) 

 

SET @orig_lat=@LATITUDE1 set @orig_lng=@LONGITUDE1 

 

DECLARE @orig geography = geography::Point(@orig_lat, @orig_lng, 4326); 

if @unit = 'miles' 

SELECT *, 

    @orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326))/1609.344  

       AS distance 

--INTO #includeDistances 

FROM  DEALER  dest WHERE ([LATITUDE] IS NOT NULL AND LONGITUDE IS NOT NULL) 

ORDER BY DISTANCE ASC

else

SELECT *, 

    @orig.STDistance(geography::Point(dest.Latitude, dest.Longitude, 4326))/1000

       AS distance 

--INTO #includeDistances 

FROM  DEALER  dest WHERE ([LATITUDE] IS NOT NULL AND LONGITUDE IS NOT NULL)

ORDER BY DISTANCE ASC 

 

END

CHECK FOR DATA USING NOT EXISTS IN B CHILD TABLE COMPARED TO A SOURCE TABLE

CHECK FOR DATA USING NOT EXISTS IN B TABLE  COMPARED TO A SOURCE TABLE

SELECT ID

FROM  ITEMSA(NOLOCK) A

WHERE NOT EXISTS

(

    SELECT INVOICEITEM_ID

    FROM  ITEMSB (NOLOCK) B

    WHERE A.Id = B.ID

)

GO

SELECT DISTINCT A.ID

FROM ITEMS(NOLOCK) A

LEFT JOIN JLRMCTPRD.[PRDDOL].[INVOICEITEMS] (NOLOCK) B

    ON A.Id = B.INVOICEITEM_ID

WHERE B.INVOICEITEM_ID IS NULL

-- using EXISTS to check for existence

SELECT P.ProductID

FROM Production.Product p

WHERE EXISTS

(

    SELECT 1

    FROM Sales.SalesOrderDetail sod

    WHERE sod.ProductID = p.ProductID

)

-- Using LEFT JOIN to check for existence

GO

SELECT DISTINCT P.ProductID

FROM Production.Product p

LEFT JOIN Sales.SalesOrderDetail sod

    ON sod.ProductID = p.ProductID

WHERE sod.SalesOrderDetailID IS NOT NULL

REFERENCE: http://sqlserverplanet.com/tsql/comparing-exists-vs-left-join-where-not-null

LogParser

Download Link:

 

http://www.microsoft.com/en-gb/download/details.aspx?id=24659

 

Study Materials:

https://sqlbits.com/Sessions/Event9/LogParser-quicker_than_SSIS_easier_than_BCP

echo off

 

echo off

cls

cd\

 

cd "c:\Program Files (x86)\Log Parser 2.2\"

LogParser -i:TSV -iSeparator:"|"  "select BusinessEntityID,PhoneNumber,PhoneNumberTypeID ,ModifiedDate from C:\temp\personphone.csv" -o:datagrid

 

LogParser -i:TSV -iSeparator:"|"  "select * from C:\temp\personphone.csv" -o:datagrid  -- To select all the rows - This single delimited pipe works even if the file contains two pipe delimited

 

LogParser -i:TSV -iSeparator:"|"  "select * from C:\temp\personphone.csv where Rownumber = 10" -o:datagrid  -- To select only the specific rownumber

 

Chart

logparser  "select date,cost  into c:\temp\expense.gif from c:\temp\fahimexpense.txt" -i:csv -o:chart -chartType:barstacked -view:on -values:on

The below logparser will truncate the data from the table client2file

create table dbo.client2file (customerid int null,email varchar(100))

go

select * from dbo.client2file

LogParser -i:csv  "select customerid,email  into dbo.client2file from C:\Backup\FTP\client2file15032015.txt" -o:sql  -server:localhost  -database:test  -cleartable:ON -transactionRowCount:-1

 

--The below logparser won't truncate the data from the table client2file

LogParser -i:csv  "select customerid,email  into dbo.client2file from C:\Backup\FTP\client2file15032015.txt" -o:sql  -server:localhost  -database:test  -cleartable:OFF -transactionRowCount:-1

The below logparser is to insert additional filename and rownumber column into the table

 

create table dbo.client2filewithfilenamerownumber  (filename sysname,rownumber int,customerid int null,email varchar(100))

go

select * from dbo.client2filewithfilenamerownumber

LogParser -i:csv  "select filename,rownumber,customerid,email into client2filewithfilenamerownumber from C:\Backup\FTP\client2file15032015.txt" -o:sql  -server:localhost  -database:test  -cleartable:ON -transactionRowCount:-1