Wednesday, June 18, 2014

VI Editor Commands in Linux



Vi editor commands


Input commands with (esc or ESCape)

Character
Description
-a-  
Appends after cursor
-i-
Inserts before cursor
-o-
Opens a new line below the cursor
-O-
Opens a new line above the cursor
-dw-
Deletes single word
-dd-
Deletes entire line
-u-
Undo last change
-U-
Undo all changes of single line
-yy- or -Y-
Copies single line
5yy
Copies 5 lines
5dd
Deletes 5 lines
-p-
Paste the line after the cursor
-P-
Paste the line before the cursor
-x-
Deletes a character
:w
Saves file with the given name
:wq
Save and exit from vi
:q!
Quit without save
ZZ
Same as wq
G
Goto last line
6G
Goto specified line number
Ctrl+g
Displays current line number and file information
/searchstring
Searches for a string in the file
-n-
Repeats the search towards down (Find Next)
-N-
Repeats the search towards up (Find Previous)
:set list
Show invisible characters
:set nolist
Don’t show invisible characters
:set number
Displays line numbers
:set nonumber
Line numbers off
:set ignorecase
Ignore the case (helps while searching for a string in the file)
:set noignorecase
Switch off the ignore case option
:%s/Historical/historical_new /g   
Replaces string Historical to historical_new
% -  All lines
/g  - Replace all occurrences in all lines
:5,20s/kalyan/kalyan_new /g
Replaces string from 5 to 20 lines only
:%s/SearchString/Replacewith /gi
-gi - Indicates replace string globally ignores case
:s/Linux/Unix
Replace only in current line, If you include %infront of s it replaces all lines


 

Tuesday, June 17, 2014

Filestream and FileTables in SQL Server


Managing Unstructured Data in SQL Server
 
Prior to SQL Server 2008, storing and managing unstructured data is a bit difficult one,  for that we have only two options varbinary and image fields to store unstructured information. Though it provides transactional consistency it badly hurts the performance. The other approach many people follow is storing the path of unstructured data in the table, it gives good performance but it has its own side effects like it doesn’t ensure transactional consistency and it is difficult while doing backup and restore we need to make sure the physical paths should available in the target server.
 
FILESTREAM -- A new feature introduced in SQL 2008 which allows you to store BLOBS (word documents, images, pdf files) in the NTFS file system instead of database. As it is NTFS SQL Server takes an advantage of streaming capabilities and maintains transactional consistency of the data. While accessing FILESTREAM it uses streaming API and it will not use SQL Sever buffer pool, hence it will not SQL Server memory for processing. FILESTREAM is not a datatype and it is a attribute assigned to varbinary(max) column, then it becomes filestream enabled column.
If you store any image or pdf on the filestream enabled column then only pointer to the file will be stored in the column and the actual file will get stored in the NTFS.
 
By default installation this feature will be in disable state, In order to use this feature it has to be enabled.
 
To enable FILESTREAM and It can be enabled in three levels :
 
  1. Configuration Manager - SQL Server - Properties - On FileStream Tab - Check the below items
    1. Enable FileStream for Transact-SQL Access
    2. Enable FileStream for file I/O Access
    3. Windows Share Name
      Specify Share Name SQL2012_FileTables
    4. Allow remote clients access to filestream data
 
Enable FileStream for File I/O Access                  -- If you are interested in enabling read and write FILESTREAM data from windows then we need to check this check box.
 
Allow remote client access to filestream data   -- If remote clients need to access the file share using their stored procedure or application this feature must be enabled.
 
Using T-SQL
 
Level: Level of FILESTREAM functionality to enable, a integer between 0 and 3. If not specified, level 3 is default.
 
0 - FILESTREAM functionality disabled.
1 - Enable FILESTREAM for T-SQL access.
2 - Enable FILESTREAM for T-SQL and file I/O streaming access but do not allow remote clients file I/O streaming access.
3 - Enable FILESTREAM for T-SQL and file I/O streaming access and allow remote client streaming access.
 
Share: Name of windows share that is created for file I/O streaming access to FILESTREAM data. If not specified, instance name is default.
 
 
The major difference between FILESTREAM enabled and a normal database is
 
For a normal database we will be having only MDF and LDF files
For a FileStream enabled database will be having MDF, LDF and FileStream Container
 
FILESTREAM Container is a special folder in the NTFS where SQL Server will store all the FILESTREAM data as files in the disk.
 
 
CREATE DATABASE FileStreamTest ON
PRIMARY ( NAME = FSTreamData, FILENAME = 'C:\Temp\FStreamData.mdf'),
FILEGROUP FStreamFolder CONTAINS FILESTREAM( NAME = FStreamFolder, FILENAME = 'C:\Temp\FStreamFolder')
LOG ON ( NAME = FStreamLOG, FILENAME = 'C:\Temp\NP\FStreamLOG.ldf')
GO
 
In the above statement 3rd line on CREATE DATABASE Statement shows FILSTREAM Container, which creates a folder in C:\Temp with name FStreamFolder, Under which there are two folders $FLOG and filestream.hdr, The $FLOG is similar to transaction log file in the database and filestream.hdr consists metadata information used by SQL Server, (Please don’t make any changes to that file).
 
Now Creating a table with FileStream Enabled column
 
Use FileStreamTest
go
Create table CARS (CARID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
CARIMAGE varbinary(max) FILESTREAM Null)
 
For every FILESTREAM enabled table you should create a column with UNIQUEIDENTIFIER ROWGUIDCOL.
 
After executing the above command a new table will get created on the FileStreamTest Database and if you observe the c:\temp\Fstreamfolder a new folder will be created with GUID as its name, SQL Server will creates one folder per each table when there is FILESTREAM enabled column exists.
 
To Store and retrieve information from a FILESTREAM Enabled column
 
-- Variable to Store ImageData
 
DECLARE @img AS VARBINARY(MAX)
SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK 'C:\temp\tulip.jpg', SINGLE_BLOB ) AS x
 
INSERT INTO CARS(CARID, CARIMAGE)
 SELECT NEWID(), @img
 
select * from CARS
 
Select carid, CARIMAGE.PathName() from CARS
 
 
From SQL Server 2012
 
In SQL Server 2012 FileTable concept was introduced and it will get more simplified than FILESTREAMs, File table is a feature newly introduced in SQL Server 2012 is an enhancement of FILESTREAM which was introduced in SQL Server 2008.
 
FileTable is a special where you need not to specify column definition while creating a filetable, the schema for the filetable is fixed.
 
Below is the syntax for creating FILETABLE in SQL Server 2012
 
CREATE DATABASE FileTeableDemo
ON
PRIMARY ( NAME = filetable_data,FILENAME = 'C:\temp\filetable.mdf'),
FILEGROUP FileTableFG CONTAINS FILESTREAM(NAME = FileTableDemo, FILENAME = 'C:\temp\FileTableDemo')
LOG ON ( NAME = filetable_log,FILENAME = 'C:\temp\filetable.ldf')
WITH FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = 'FileTableFolder')
GO
 
 
SELECT * FROM sys.database_filestream_options WHERE database_id=DB_ID('FileTeableDemo')
 
 
 
USE FileTeableDemo
GO
CREATE TABLE MyDocuments AS FileTable
CREATE TABLE MyImages AS FileTable
 
To Insert any records into FILETable that can be done in two ways: 1. Using T-SQL Like we did for Filestream and 2. Right Click on FileTable on which you want to insert records - Explore file table - Copy and paste images into it.
 
select * from mydocuments
select * from myimages
 

Thursday, June 12, 2014

Basic Linux commands for DBA's Part IV


Compression Related

 
Command      
Description
Examples
 gzip
Compress the files, Each single file will be compressed as one file and a new file will get created with filename.gz
 
 
-c -- If you specified -c it displays standard out to console and creates a new gz file without delete the original file
 
 
 
$gzip *.err    -- Compress all .err files and creates new file per .err with .err.gz
$ gzip errorlog
$ ls - lrt
 errorlog.gz
 
$ gzip -c errorlog > errorlog1.gz
$ ls -lrt
 errorlog
 errorlog1.gz
 
$ gzip *.err
 
 gunzip
Uncompress the file that was compressed using gzip or compress commands
 
 
-c -- displays standard output to console and without unzip the file
 
 
$  gunzip *.err.gz -- Uncompress all .err.gz files into .err files
 
 
$ gunzip errolog.gz
 
$ ls -lrt
 errorlog
 
$  gunzip *.err.gz
 tar
Tar stands for Tape Archive in Linux. This command is used to create a archive for collection of files into highly compressed files in unix / linux systems
 
Tar Examples
 
-c -- Creates a new .tar archive file
-v -- Verbose mode, shows the progress of .tar file
-f -- filename
 
[root@Kalyan workspace]# tar -cvf mysqlbinaries.tar /local/mnt/workspace/mysqlbinaries/
tar: Removing leading `/' from member names
/local/mnt/workspace/mysqlbinaries/
/local/mnt/workspace/mysqlbinaries/MySQL-a.x86_64.tar
/local/mnt/workspace/mysqlbinaries/MySQL-1.rhel5.x86_64.rpm
/local/mnt/workspace/mysqlbinaries/MySQL-.rhel5.x86_64.rpm
/local/mnt/workspace/mysqlbinaries/MySQL-embedded-rhel5.x86_64.rpm
/local/mnt/workspace/mysqlbinaries/MySQL-devel-rhel5.x86_64.rpm
/local/mnt/workspace/mysqlbinaries/MySQL-shared-rhel5.x86_64.rpm
/local/mnt/workspace/mysqlbinaries/MySQL-test-.rhel5.x86_64.rpm
 
 
[root@Kalyan workspace]# ls -lrt
total 374356
drwxr-x--- 2 kalyan users      4096 May 27 04:07 mysqlbinaries
-rw-r--r-- 1 root   bin   382955520 Jun  2 06:50 mysqlbinaries.tar
 
 
If you observe the above command tar -cvf, It just creates a .tar files with the collection files in the mysql binaries folder, but it wont compress the collection size.
 
[root@Kalyan mysqlbinaries]# du -h .
366M    .
[root@Kalyanmysqlbinaries]# cd ..
 
[root@Kalyanworkspace]# du -h mysqlbinaries.tar
366M    mysqlbinaries.tar
 
The size of whole mysqlbinaries folder and size of .tar file is same.
 
-z -- This option helps to create a compressed gzip archive file.
 
$ tar -cvzf mysqlbinaries.tar.gz /loca/mnt/workspace/mysqlbinaries
 (OR)
$ tar -cvzf mysqlbinaries.tgz /loca/mnt/workspace/mysqlbinaries
 
 
[root@kalyan workspace]# du -h mysqlbinaries.tgz
358M    mysqlbinaries.tgz
 
[root@kalyan workspace]# du -h mysqlbinaries.tar
366M    mysqlbinaries.tar
 
-j -- This option is used to create bz2 Archive file, it compress and creates archive file less than the size of gzip but it will take more time to compress an decompress the file. It is used to create highly compressed file (.tar.bz2 or tbz or tb2)
 
 
Untar
 
-x -- This option is used to untar or extract the contents of an archive file
 
-C -- Usually if you untar any file using -x option then it will extract the contents to the current folder, if you want to change the extract location to some other folder you need to specify -C option
 
$ tar -xvf abc.tar      -- Extracts the contents to current folder
 
$ tar -xvf abc.tar -C /usr/kalyan/tmp  -- Extracts the contents to specified folder
 
-t -- This option helps to list the contents of any tar file without extracting it
 
$ tar -tvf abc.tar            -- Lists the contents of the given tar file
 
--extract -- This option helps to extract only particular file from the .tar file
 
 
$ tar --extract --file=<.tarfile> 
 
$ tar --extract --file=mysqlbinaries.tar local/mnt/workspace/mysqlbinaries/MySQL-client-advanced.x86_64.rpm
 
-r -- This option helps to add / append a file to existing tar file
 
 
$ tar -rvf
 
$ tar -rvf mysqlbinaries.tar local      --- It adds local folder to mysqlbinaries.tar file
 
$
 
 

Wednesday, June 11, 2014

Basic Linux commands for DBAs - Part III


Search Related

Command
Description And Example
 grep
 The grep command is used to search for a text in the file or from a list
 
 $ grep "linux" file1.txt           -- Search for lines containing "linux"
 $ grep kalyan/etc/passwd   -- Search for lines containing "kalyan"
 
-i- case insensitive serach
 
$ grep -i "kalyan"  file1.txt     --Search for lines containing "kalyan" and ignore the cas
 
-n displays line number
 
$ grep -i -n "linux" t1.txt     -- Search for lines containing "linux" and ignore the case and displays line number on which line it find the word linux
 
Search string in multiple files
 
$ grep -i "linux" *.txt           -- Search for lines containing "linux" in all *.txt files
 
-c Displays Occurrences counts from
 
$ grep -ic "linux" *.txt          -- Search for lines containing "linux" in all .txt files and displays only occurrences count
 
-v Inverts the search to display lines that doesn't match
 
$ grep -vic "linux" *.txt           -- Displays lines that doesn't has "linux" word on it and displays only occurrences count
 
Regular Expressions with Grep
 
^d        -- Displays lines start with d
 
$ grep "^t" -in t1.txt               -- Displays lines start with T and -i- is used to ignore the case
 
$ grep "^[TL]" -in t1.txt           --Displays lines start with either T or L
 
$ grep "[^0-9]" -in t1.txt           --Displays lines contains atleast one-numeric value, the meaning of ^ caret will change if you                
                                                        enclosed with square braces.
 
$ ps -ef | grep "mysql"            --Displays processes which has mysql in it.
 
$ ls - l | grep "^d"                     --Displays only directories
 
$ grep "x$" t2.txt                      --Displays lines ends with "x"
 
$ grep "5\.." t1.txt                    --Suppose we have data in a file with version numbers like 5.1, 5.x and 5.5 like that, It will search those lines and give you output
 
$ grep "[A-Z][A-Z]"  t1.txt         --Displays lines containing two capital letters (Ex : EA, SA etc.,)
 
 
 find
Find command is used to locate files in the linux / unix server
 
Syntax  : fine where-to-look criteria what-to-do
 
All arguments to find command are optional.
 
$ find                              -- Displays paths of all files in the current directory and sub-directories.
 
$ find . -print                 -- Displays exact same result as above command
 
$ find / -name "test.txt" -- Search for the file test.txt on all the current file system
 
$ find . -name "Kalyan*" -- List filenames starts with Kalyan, If you wan to ignore case then use -i.
 
$ find . -name "*.txt"      -- Lists  only .txt files
 
We can also specify multiple locations as below to search for any particular file
 
$ find /var/lib /usr/tmp $HOME -name "*.txt"           -- File locations must be separated by space.
 
-type option is a parameter to separate the files and directories which can be used along with find command, This can be used only if you want to make your search specific to one particular type
 
$ find . -type f -name "t1.txt"                --Search for a file with name t1.txt in the current location
 
$ find . -type d -name "binaries"          -- Search for a directory name binaries in the current location
 
$ find . -type f -name "*.java"
 
$ find . -type f -perm 0555 -print           -- Displays files which has permissions 555 (read and execute for all )
 
$ find . -type f -perm 0777 -print -exec chmod 644 {} \; -- Search for files which has 777 and changes permissions to 644
 
$ find . -type f -name "t1.txt" -exec rm -f {} \;     -- find and remove t1.txt file in the current folder
 
$ find . -type f -empty            -- Find all empty files
 
$ find . -type d-empty            -- Find all empty directories
 
-user  -- This option helps to find files based on the owner type
 
$ find . -user root       -- Displays files whose owner is root in the current directory
 
$ find . -group bin         -- Displays files whose group is bin in the current directory
 
$ find . -group bin -name "t2.txt"       -- Search files by group
 
-size and -mtime  -- Size option is helps to search files by size and mtime helps to search files by modification time
 
$ find . -size +10M          --Find files bigger than 10 MB size in current directory
 
$ find . -size -10M           --Find files lesser than 10 MB size in current directory
 
$ find . -mtime 1             --Search files and folders that are modified in last 24 hours
 
$ find . -mtime 10           --Search for files and folders that are modified in last 10 days
 
$ find . -mtime 10 -type f    -- Search for only files that are modified in last 10 days
 
$ find . -mtime 10 -type d    -- Search for only directories that are modified in last 10 days
 
Advanced
 
$ find . -type f \( -name "*.txt" -o -name "*.t" \)   -- Search files with multiple extentions
 
Find and Copy
 
find . -type f \( -name "*.txt" -o -name "*.t" \) -exec cp {} txtfiles
 
 find . -type f -name "*.txt" -exec grep -i "linux" {} \;
 

 

Friday, June 6, 2014

Basic Linux commands for DBAs - Part II




Permissions Related

Command
Description And Example
chmod
With help of this we can change the file permissions of a file, which can be assigned permissions to user, group and others
4 - (read), 2 - (write) and 1 - (execute).
chmod 777 filename -- read, write and execute for all
chmod 755 filename --rwx for owner, rw for group and others.
-rwxrw-rw-
First "-" indicates it’s a normal file if first character is "d" then it is a directory, first set or rwx (read, write and execute) belongs to owner, and second set of rwx belongs to group and third set of rwx belongs to others.
chown
Helps to change the owner and group of the file
$ chown filename
$ chown kalyan file1 -- It changes the owner of file to kalyan login
How to change the owner and group
$ chown :[groupname] filename
$ chown kalyan:users file1 -- It changes the owner as "kalyan" and group as "users" for file1
How to change owner to all files in any folder
$ chown -R
$ chown -R Kalyan:Users /usr2/kalyan/jpgs
chgrp
Helps to change the group of the file, The difference between chown and chgrp is, using chown you can change the owner and group of a file, but using chgrp you can change on the group of the file not the owner
$ chgrp filename
$ chgrp bin /usr2/kalyan/myfile -- It changes the group of myfile to bin
$ chgrp -R bin /usr2/kalyan/executables/ - It changes the group to bin for all files in the given path



Disk Space Related



Command
Description And Example
df
The df command stands for "disk filesystem", It displays how much space is occupied and how much space is available on file systems not directories and files
$ df -- Displays file systems usage statistics when you run without any argument
$ df -h -- Displays in human readable format
$ df -h test -- It displays usage information of the filesystem where test resides, it wont give you size of test directory.
-i- -- Displays inode information instead of block usage
$ df -i. -- Displays inode information instead of block usage
Switches
-a -- Shows all file system disk usage
-k -- Displays output in Kilobytes
-i- --Displays inode information
-t --Displays disk space usage for each of the specified type of file system
-T --Displays file system type
du
The du command stands for "disk usage", this command will display usage information of a directory and sub-directory and displays the blocks that are occupied.
Switches
-a -- Write counts for all files, not just directories
-h -- Displays output in human readable format
-c -- To get the total usage in the output
-s -- To get only total size in form of summary
$ du -h -- Displays usage information of the given directory
$ du -sh --Displays only size of the directory (nothing else will be printed)
$ du -ahc --Displays all files human readable format including total size of the folder
$ du -sh --exclude="*.txt" --Exclude the .txt files and provides the size of remaining all files


Thursday, June 5, 2014

Basic Linux Commands for DBAs


General Linux Commands for DBAs
 

Command      
Description
Examples
 pwd
Displays current working directory
$ pwd
/usr/kalyan
 ls
List the current directory contents
$ ls
 ls -l
List the current directory contents with extra details
$ ls -l
 ls
Displays files for the specified path
$ ls
 cd ~ / cd
User home directory
$ cd  / $ cd ~
 cd -
Previous current directory
$ cd -
 clear
Clear screen
$ clear
 touch                                                                
Creates an empty file
$ touch abc
 rm
Removes specified file
$ rm abc
 rm -rf
-f force -r recursive, Removes complete directory with its contents
$ rm -rf
 cp file1 file2
Creates a copy of file1 to file2, if destination is file it creates a duplicate copy, if destination is directory it moves the file into that directory.
$ cp file1 file2
 cp -r dir1 dir2
Creates a duplicate copy of whole directory
$ cp -r dir1 dir2
 mv file2 file3
Renames given file to new file
$ mv file2 file3
 cat
Displays contents of the file
$ cat file1.txt
 more
Displays contents of the file page by page, for next page {Space}  to quit {q}
$ more file1.txt
 head
Displays first 10 lines from the file
$ head file1.txt
$ head -20 file1.txt (first 20 lines)
 tail
Displays last 10 lines from the file
$ tail file1.txt
$ tail -20 file1.txt (last 20 lines)
 tail -f
Displays contents of the file as it grows, starting with last 10 lines, to quit {ctrl+c}
$ tail -f Kalyan2.err
 date
Displays current date and time
$ date
 man
Display help for the given command
$ man cat
 command > filename
Redirects output to specified filename
$ date > date.txt
 command >> filename
Appends the redirected output to existing file
$ date >> date.txt
 sort file1
Sort the contents of the file
$ sort file1
 | (Pipe)
Pipes the output to other command
$ cat file1 | sort
 ps
Displays all current processes
$ ps
 top
Displays all running processes
$ top
 kill
Kills the process Id
$ kill
cal
Displays current months calendar
$ cal
 uptime
Displays current uptime
$ uptime
 w
Displays who is online (All the users who are connected to this host)
$ w
 whoami
Displays the current logged in user name
$ whoami
 finger
Displays information about the user
$ finger kalyan
 whereis
Displays possible locations of the given application
$ whereis mysql
$ whereis perl
 which
Displays which app runs
$ which mysql
$ which perl
 uname
Prints the system information
$ uname
 uname -a
Prints Kernel information
$ uname -a
 uname -o
Displays operating system
-o Operating System
-v Kernel version
-n Node Name
-m Machine
-p Processor
-a All
$ uname -o
 wc
Displays number of newlines, words and bytes in the file
$ ls -lrt | wc - l
Displays count for ls -lrt
 
 set
Displays the name and value of each shell vairable
$ set
 $HOME
To view value in any environment variable
$ HOME
 printenv
Displays all environment variable
$ printenv