Desktop Linux

I've been running Ubuntu on my desktop, since leaving Novell back in 2012.  I was feeling a bit nostalgic the other day, and decided to build out a VM with the latest version of OpenSUSE.  Let me share a few bullet points with you about this experience.

1.  The install of OpenSUSE took twice as long.

2.  Boot time is incredibly slow with OpenSUSE.  Honestly, I can start booting it, go get a drink, startup my Ubuntu VM, reboot it a couple of times, get up and go to the bathroom, come back and OpenSUSE will still be booting.

3.  Response time is even slower.  It's painful actually.

4.  The update process is HORRIBLE.  When OpenSUSE first popped up and told me I had 174 updates, I thought, OK, no big deal, right?  Well the graphical update went into some sort of infinite loop.  I had to kill it after about the 6th time it restarted itself.  In the end, I opted for a simple "zypper patch" as root at the command line.  That  sucker has been running for over 2 hours.  By comparison, Ubuntu updates rarely take in excess of 5 minutes.

As much as I miss the good old Novell days, I think I am converted to running Ubuntu.

I must say, running OpenSUSE in seamless mode does  rock.  It is the one thing I've found it does better than Ubuntu.

Access SQL Server from Linux

Let's face it.  Even since back before they started labeling the sql server releases as 2000 whatever, there has been a need to access sql server from other operating systems.  I'm sure java would be a slick way to go, but for an  administrator used to living at a bash prompt, that just won't cut it.

Thankfully, as part of some consulting work, I stumbled upon FreeTDS.  It's pretty slick.
1.  Download FreeTDS from http://www.freetds.org/

2.  Install FreeTDS
 - copy the compressed file to the target server.
 - uncompress the software bundle.
 - cd into the FreeTDS source.
 - ./configure
 - make
 - make install
 - the default is to install to /usr/local

3.  Configure FreeTDS
 - cd /usr/local/etc
 - vi freetds.conf
 - add in a line for the target server
 # A typical Microsoft server
 [EvolveDB]
     host = 137.65.51.90
     port = 1433
     tds version = 7.0

4.  Query your server
 tsql -S <your name from the freetds.conf file> -U <username> -P <password>
 If my user was admin and my password was friday, server name EvolveDB
 tsql -S EvolveDB -U admin -P friday

5.  Use tsql in batch mode
 tsql is very limited.  Simply use redirects to either run scripts or capture output.
 For example, using the above connection, and running a script named mike.sql and capturing that to mike.out
 tsql -S EvolveDB -U admin -P friday < mike.sql > mike.out

I've found that tsql does some funky things with the output.  For example, you can pipe it directly to awk, but if you toss sed in there as well to just remove spaces, it blitzes the tabs as well.  I've had the best luck sticking a ~ in as a delimiter, then using that with awk to parse the columns back out.

Test writing to an Oracle directory

Let's say you want to make sure you can write to a directory you created.

1.  select * from dba_directories where directory_path = '/usr/tmp';

2.  In my case, I had three.  I decided to test the ODPDIR directory.

3.
DECLARE
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'ODPDIR', 'ODPDIR.txt', 'W' );
  utl_file.put_line( l_file, 'Here is some text' );
  utl_file.fclose( l_file );
END;
/

4.  I ssh into my server, cd to /usr/tmp, and I see a file called ODPDIR.txt has been created.  Whoot!

Utility and Setup for Testing JDBC Connections

This is a Windows based solution.  Sorry Linux fans.  The utility is available in a Linux format, so I would assume this could easily be ported to Linux.

This was used for testing Oracle connections, but the utility should support any standard jdbc connection.

1.  Ensure java is installed and in your PATH.

2.  Download Jisql from http://www.xigole.com/software/jisql/jisql.jsp.  Note the names in the uncompressed LIB directory.

3.  Ensure you have the Oracle JDBC drivers installed and note the path to the jar files.

4.  Now, you should be ready for some simple testing.  A simple batch file like this will do the trick.
    @ECHO OFF

    :LOOPIT

    echo Query instance name from db

    date /t >> logfile.out

    time /t >> logfile.out

    echo First attempt using SID >> logfile.out

    java -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql -user system -password XXXXXXX
         -driveroraclethin -cstring jdbc:oracle:thin:@address-scan:1521:sidname-c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out

    echo Second attempt using SERVICE_NAME >> z:\mike\runit.out

    java -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql -user system -password XXXXXXX
    -driver oraclethin -cstring jdbc:oracle:thin:@//address-scan:1521/servicname.yourcompany.com -c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out
   
    choice /n /t 10 /D Y /M "pausing 10 seconds"

    goto LOOPIT

5.  You can alternatively use TNS entries for testing the connections.  This was the best way I found to test a more complicated setup, such as load balanced, fault tolerant connection.  Something like this: <em>(note - I made a copy of the tnsnames.ora file to c:\temp then trimmed it down to just have the two entries I wanted to test)</em>
    @ECHO OFF

    :LOOPIT

    echo Query instance name from db

    date /t >> logfile.out

    time /t >> logfile.out

    echo First attempt using TNSENTRY>> logfile.out

    java -Doracle.net.tns_admin="c:/temp/" -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql
    -user system -password XXXXXX -driver oraclethin -cstring jdbc:oracle:thin:@TNSENTRY-c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out

    echo Second attempt using TNSENTRY >> logfile.out

    java -Doracle.net.tns_admin="c:/temp/" -classpath lib/jisql-2.0.11.jar;lib/jopt-simple-3.2.jar;lib/javacsv.jar;D:/app/melquist/product/11.2.0/client_1/jdbc/lib/ojdbc6.jar com.xigole.util.sql.Jisql
    -user system -password XXXXXX -driver oraclethin -cstring jdbc:oracle:thin:@WAREHOUSE3 -c ; -query "select host_name from gv$instance where instance_number=userenv('instance');" >> logfile.out
   
    choice /n /t 10 /D Y /M "pausing 10 seconds"

    goto LOOPIT

Commit Strategy for Large Transactions

Let's face it.  Every DBA has come across a need to update a LOT of records.  The problem usually is that a mass update can take forever, blow out your redo logs, lock out other access, etc.  Here is the solution.  This was written and used in Oracle, but I'm thinking could easily be modified to work with any ANSI compliant relational database.

DECLARE

CURSOR cur IS
   select * from owner.table_temp;
                 
ct NUMBER(5) := 0;
group_size NUMBER(5) := 5000;

BEGIN
    FOR srmvt in cur LOOP
        INSERT INTO owner.table   (<columns>)
            VALUES  (srmvt.<columns>);
       ct := ct + 1;
       IF ct >= group_size THEN
           commit;
           ct := 0;
       END IF;
   END LOOP;
   commit;
END;

I have also used this for updates as well.

DECLARE
CURSOR cur2 IS  
   select * from yourcompany.yourtable;
                
ct2 NUMBER(5) := 0;
group_size NUMBER(5) := 5000;
BEGIN
   FOR srmvt2 in cur2 LOOP
        update yourcompany.yourtable set process_flag = 'SUCCESS'
        where transactionid = srmvt2.transactionid;
       ct2 := ct2 + 1;
       IF ct2 >= group_size THEN
           commit;
           ct2 := 0;
       END IF;
   END LOOP;
   commit;
END;
/

Exclusion List Script

This may not be of interest to most of my blog readers, and I apoligize for that. However, after struggling with a good way to accomplish what I needed to for days, and now having come up with a somewhat good solution, I wanted to publish it out here so I don't lose it. No sense reinventing the wheel, right?

My problem was I needed a good way to track an exclusion list. Normally I'd dump it into a table, but this particular list needed to be used on dozens of databases. I didn't want to maintain multiple copies of the same data - that would be a nightmare. And I didn't want to create dozens of database links - I don't really like them anyway. I thought about hard coding the list in my script, but that seemed kind of ugly. And the list needs to be sql ready, which means each element needs to be enclosed in single quotes and be comma delimited. Let me tell you, bash was not crazy about the quotes. At one point, I had the list hard coded in my script, and had escaped the quotes. Not pretty. It looked something like this:
EXCEPTIONLIST="\(\'one\'\,\'two\'\,\'three\'\)"

Bare in mind the end result for the sql query just needs to be:
('one','two','three')

Option one. I created a table in my central database. I then queried it one time, put it in a variable, then used that for the queries to all the other databases. The bash code looks like this:
EXCLUDED_USERS=`sqlplus -s " / as sysdba"  &lt;
set pages 9999
set lines 132
set trim on
set trims on
set echo off
set recsep off
set head off
set feedback off
set newpage none
select username from owner.table_name;
exit;
EOF`

echo $EXCLUDED_USERS
# Init the variable, put on the left paren.
DELIMITED_LIST=`echo -ne '('`
for USER in $EXCLUDED_USERS
do
# reconstruct the list, but add in the commas and single quotes.
DELIMITED_LIST=`echo $DELIMITED_LIST"'"$USER"',"`
done
# Remove the last comma we really did not need.
DELIMITED_LIST=$(echo ${DELIMITED_LIST%\,})
# Add in the right paren.
DELIMITED_LIST=`echo -ne $DELIMITED_LIST")"`

Option two. Just for grins, I wanted to see if it would work with a space delimited variable list in the script. It looks like this. I ripped out the actual sensitive data.....

# Exclude listing. Put into multiple vars for readability.
# If you add a line, make sure you add it to the line that puts them all together.
# One space between entries please.
EXCLUDED_USERS1="blah blah2"
EXCLUDED_USERS2="blah3 blah4"
EXCLUDED_USERS=$EXCLUDED_USERS1$EXCLUDED_USERS2$EXCLUDED_USERS3$EXCLUDED_USERS4$EXCLUDED_USERS5

# Make sure we are set to a space delimiter
IFS=" "
# Init the variable, put on the left paren.
DELIMITED_LIST=`echo -ne '('`
for USER in $EXCLUDED_USERS
do
# reconstruct the list, but add in the commas and single quotes.
DELIMITED_LIST=`echo $DELIMITED_LIST"'"$USER"',"`
done
# Remove the last comma we really did not need.
DELIMITED_LIST=$(echo ${DELIMITED_LIST%\,})
# Add in the right paren.
DELIMITED_LIST=`echo -ne $DELIMITED_LIST")"`
EXCLUDED_USERS=$DELIMITED_LIST

The basic principle is the same either way. Capture the list into an array, then walk through the array, putting the quotes, commas and parentheses back in. Then it's good to go for the actual query:

USERLIST=`sqlplus -s "/as sysdba"   &lt;
set pages 9999
set lines 132
set trim on
set trims on
set echo off
set recsep off
set head off
set feedback off
set newpage none
select username
from dba_users
where account_status = 'OPEN'
and username not in
(select distinct owner
from dba_objects)
and username not in $EXCLUDED_USERS
order by username
/
exit
EOF`

And that is more than you ever wanted to know about working in IT.......

Sql Server status sql script

Yeah, I know.  Another boring work post.  I have to save these things somewhere!

/* The Sql Server mail procedure does not like single quotes or #TEMP tables.

To get around this, we use some tables but drop them when we are done.
*/

use master
go

/* Get the error messages, agent.
*/

CREATE TABLE Errors (LogDate datetime, ErrorLevel int, vchMessage varchar(255))
CREATE INDEX idx_msg ON Errors(LogDate, vchMessage)
INSERT Errors EXEC xp_readerrorlog 0, 2
go

/* Get the error messages, server.
*/
CREATE TABLE Errors2 (LogDate datetime, ProcessInfo varchar(255), vchMessage text)
CREATE INDEX idx_msg2 ON Errors2(LogDate)
INSERT Errors2 EXEC xp_readerrorlog 0, 1
go


SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
into BackupResults
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name
go

/* We really don't need to see the backup messages that get written to the log.
*/
delete from Errors
WHERE vchMessage LIKE '%Log backed up%' or
vchMessage LIKE '%.TRN%' or vchMessage LIKE '%Database backed up%' or
vchMessage LIKE '%.BAK%' or vchMessage LIKE '%Run the RECONFIGURE%'
go

delete from Errors2
WHERE vchMessage LIKE '%Log backed up%' or
vchMessage LIKE '%.TRN%' or vchMessage LIKE '%Database backed up%' or
vchMessage LIKE '%.BAK%' or vchMessage LIKE '%Run the RECONFIGURE%'
go

/* Move to target database, the one with filegroups that tend to fill up.
Create the temp table back in master, though. That is where the others are.
*/
use target
go

SELECT
cast(b.groupname as varchar(30)) [groupname],
Name,
[Filename],
CONVERT (Decimal(15,2),ROUND(a.Size/128.000,2))
[CurrentAllocatedMBSpace],
CONVERT (Decimal(15,2),
ROUND(FILEPROPERTY(a.Name,'SpaceUsed')/128.000,2))
AS [SpaceUsedMB],
CONVERT (Decimal(15,2),
ROUND((a.Size-FILEPROPERTY(a.Name,'SpaceUsed'))/128.000,2))
AS [FreeSpaceMB]
into master..FileGroupSpace
FROM dbo.sysfiles a (NOLOCK)
JOIN sysfilegroups b (NOLOCK) ON a.groupid = b.groupid
ORDER BY b.groupname
go

use master
go

EXEC msdb.dbo.sp_send_dbmail
@recipients=N'melquist@yourcompany.com',
@body='Status report is attached',
@body_format='TEXT',
@subject ='SQL Server Status Report',
@profile_name ='yourcompany',
@query ='EXEC master..xp_fixeddrives;
select * from Errors;
select LogDate, cast (ProcessInfo as varchar(10)) [ProcessInfo], cast(vchMessage as varchar(150)) [Message] from Errors2;
select * from BackupResults;
SELECT name "Failed Jobs" FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0;
SELECT name "Disabled Jobs" FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name;
select groupname, sum(CurrentAllocatedMBSpace) [Currently Allocated Space (MB)], sum(SpaceUsedMB) [Currently Used Space (MB)], sum(FreeSpaceMB) [Current Free Space (MB)]
from FileGroupSpace
group by groupname',
@attach_query_result_as_file = 1,
@query_attachment_filename ='QueryResults.txt'
go

Drop table Errors
Drop table Errors2
Drop table BackupResults
Drop table FileGroupSpace
go