?
Solved

Sybase script to create device

Posted on 2007-08-03
13
Medium Priority
?
3,034 Views
Last Modified: 2012-08-13
When a database device is dropped, Sybase does not remove the OS file. IS it possible to remove the file during sp_dropdevice? Is it possible to determine in a scripts if the command should be REINIT or INIT?

I am attempting to create a JDBC script that will INIT or REINIT a database device. I need to REINIT if the file exists, I need to INIT if the files does not exist.


USE master

DECLARE    @dbname                         nvarchar (50)
DECLARE    @webm_user                      nvarchar (50)
DECLARE    @webm_pwd                       nvarchar (50)
DECLARE    @webm_pwd_temp                  nvarchar (50)
DECLARE    @l_sql                          nvarchar (2000)
 
set @dbname = 'mydb'
set @webm_user = 'myuser'
set @webm_pwd = 'mypassword'

BEGIN

    -- Create device db
    IF NOT EXISTS (select 1 from master..sysdevices where name = @dbname)
    BEGIN
        set @l_sql = 'disk init name = ''' + @dbname + ''', size = ''500m'', physname= ''' + @dbname + ''''
        execute (@l_sql)
    END

    -- Create device log
    IF NOT EXISTS (select 1 from master..sysdevices where name = @dbname + 'log')
    BEGIN
        set @l_sql = 'disk init name = ''' + @dbname + 'log'', size = ''500m'', physname= ''' + @dbname + 'log'''
        execute (@l_sql)
    END

    -- Reinit device log -- in case file exists
    IF 1=1
    BEGIN
        set @l_sql = 'disk reinit name = ''' + @dbname + ''', size = ''500m'', physname= ''' + @dbname + ''''
        execute (@l_sql)
    END

    -- Reinit device log -- in case file exists
    IF 1=1
    BEGIN
        set @l_sql = 'disk reinit name = ''' + @dbname + 'log'', size = ''500m'', physname= ''' + @dbname + 'log'''
        execute (@l_sql)
    END
     
END
0
Comment
Question by:mbevilacqua
  • 6
  • 5
  • 2
13 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 19629272
You probably want to write a stored procedure to do this; the closer to the database you are, the easier this will be.

I believe the way I would approach this is to first try the INIT and check to see if it succeeded or failed.  If it failed, I would try the REINIT.

The procedure will have to do dynamic SQL but you will have to find a way to signal success or failure back to the calling routine.  the EXECUTE() is pretty bad and passing back status information.

You could create a temp persistent temp table then have the dynamic SQL block insert a status row to indicate what happened.  For instance....

CREATE TABLE tempdb..INIT_STATUS (REC_DT DATETIME, ERROR_NO INT, SQL_STAT INT)

Set a string to the following value and execute it
BEGIN
  DISK INIT NAME = MYDB SIZE = 500M PHYSNAME = MYDBDEV
  INSERT INTO tempdb..INIT_STATUS VALUES (GETDATE(), @@ERROR, @@SQLSTATUS)
END

The read the INIT_STATUS record to see if it went O.K.  If not, for the command for the REINIT just like you did for the INIT above and execute it.

The whole procedure is probably less than 50 lines.

Regards,
Bill
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 19643107
Just curious - what's wrong with just issuing both a DISK REINIT and then a DISK INIT, and deliberately expecting one of them to fail?

If the OS file doesn't exist:

- REINIT fails without consequence (who cares if we tried to use a file that isn't there, INIT will now work correctly)
- INIT succeeds

If the OS file *does* exist:

- REINIT succeeds
- INIT fails without consequence (it will not overwrite)

I'm not what REINIT does if the file *and* device already exist, so best to do it in this order (REINIT then INIT).

We'd still want some error checking just to see if we do actually care about any errors...
0
 
LVL 19

Expert Comment

by:grant300
ID: 19646551
Coincidentally, I had occasion yesterday to build a stored procedure for a client that would create, bind, and/or resize a temporary database on a tmpfs file system.

My solution was to use xp_cmdshell to remove the device file so that the disk init would always work.  In order to make this work, all you have to do is create the command string appropriate for the platform you are on and call xp_cmdshell.  On Linux, my command was...
rm -f /dev/shm/sybramdsk

The -f forces delete and never prompts which is what you need since xp_cmdshell is decidedly not interactive.

In your case you could preemptively delete the file whether it exists or not and then always do the disk init.  The only caveat is that you must configure an XP server with your database installations.  Not a big deal; you just need to make sure it got done someplace along the line.  You can always go back and add it as well.

Regards,
Bill
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:mbevilacqua
ID: 19646755
Oh boy, where do I start?

grant300
This command will not work because the dbname is dynamic:
          DISK INIT NAME = MYDB SIZE = 500M PHYSNAME = MYDBDEV
          How else can I do it without EXECUTE?

Joe:
what's wrong with just issuing both a DISK REINIT and then a DISK INIT, and deliberately expecting one of them to fail?
Failures cause scripts to stop executing.
Can you modify the script to continue through errors?

grant300:
This script is handed to customers to execute. I do not know ahead of time what OS will be used or even the file directory structure of the database server.
0
 
LVL 19

Expert Comment

by:grant300
ID: 19647652
Use a stored procedure instead of a script.  It gives you much better control, IMHO.

The parameters are dynamic.  How are they getting into the script now?

BTW, you can pass variables as arguments to disk init.  Again, in the context of a stored procedure you simply need to pass them as arguments and/or declare them and set them.

disk init name = @dbname, size = @dbsize, physname = @dbpathname, vdevno = @vdevno

Also, how are you setting the vdevno now?  That has to be culled from the system tables.

Regards,
Bill
0
 

Author Comment

by:mbevilacqua
ID: 19647872
Ahh, good info and questions Bill.

The script is shipped with these values:

set @dbname = '&$VALUE_FOR_DB'

The variables are provided by the user through a User Interface program. The program then sets the values in the script by passing the script through a variable setter function, that looks for &$ values and sets them just prior to processing.

So after processsing, the script looks like this:

set @dbname = 'mydb'
0
 
LVL 19

Expert Comment

by:grant300
ID: 19648330
If you create a stored procedure, you can do one of two things:
1)  modify the UI to call the stored procedure directly.  This can be easy or hard depending on what language and/or platform the UI is on.  Since you said it was cross platform, I am assuming it is something like Perl.  I have had good success having my client use SybPerl to make their installers much smarter.
2)  modify your script to invoke the stored proc using your same technique to substitute the arguments you will pass to the routine.  If you need the script file to stand alone, you can put the "create procedure....." for the entire stored procedure in the top of the script.

Regards,
Bill
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 19648347
Errors cause the entire script to halt??

.. oh, wait. JDBC. Huh.

Yeah, I'm with Bill on this one. I'd normally say don't use JDBC, do it all in O/S shell scripts or batch files. But if you don't know which O/S you'll be dealing with - the *only* aspect that will work the same on all platforms *and* give you the flexibility you need is in the database itself. Stored procedures for the win!
0
 

Author Comment

by:mbevilacqua
ID: 19649621
The UI installs 20 different database schemas on all of the major vendors, such as Oracle, MSSQL, SYbase, DB2. etc. It works by simply change the JDBC url to whichever database server you want to install on. We originalliy did this in O/S shells with native DB tools, but this turned out ot be a complete nightmare. The JDBC with Java UI approach is much cleaner.

I like the stored procedure approach of passing in the values. I am still not clear how to handle errors in Sybase. Can you modify the above into a stored procedure with the proper error handling and parameters?
0
 
LVL 19

Accepted Solution

by:
grant300 earned 2000 total points
ID: 19649996
Success or failure is usually determined by checking the global variable @@ERROR immediately after the statement in question.

Calls to stored procedures can be formulated to catch the return value as well...

exec @exstat = my_stored_proc @arg1, @arg2
if @exstat != 0
  begin
    .... handle error here
    return(-1) -- usually you want to get out here...
  end

How about I just clip in the routine I have been working on.

Watch out for a couple of wrapped lines.  Someday EE is going to have message entry in RTF or something similar so we can do a bit of formatting.

Bill

-----------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.sp_build_tempdb_ram') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_build_tempdb_ram
    IF OBJECT_ID('dbo.sp_build_tempdb_ram') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_build_tempdb_ram >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_build_tempdb_ram >>>'
END
go
create procedure sp_build_tempdb_ram   @dbsize varchar(32) = null
as
begin
    set nocount on
    declare @cmdstr     varchar(255),
            @log_name   varchar(32),
            @exstat     int,
            @sp_name    varchar(32),
            @vdevno     int

    set @sp_name = object_name(@@procid)

    if db_id() != 1
      begin
        print 'This procedure must be executed from the master database context'
        print 'Try invoking it:  exec master.dbo.sp_build_tempdb_ram <size qaulifier>'
        return(-1)
      end
 --
 -- Check the Sybase start script to make sure it has been
 -- edited to touch the sybramdsk device file on the tmpfs.
 --
    exec @exstat = xp_cmdshell "egrep /dev/shm/sybramdsk /etc/init.d/sybase | grep touch > /dev/null"
    if @exstat = 1
      begin
        print 'The Sybase startup script, /etc/init.d/sybase, needs to be edited.'
        print 'The "start" routine should have the following two lines added at the top.'
        print '    touch /dev/shm/sybramdsk'
        print '    chown sybase /dev/shm/sybramdsk'
        return(-1)
      end
 --
 -- If tempdb_ram does not exist or no SIZE argument is passed,
 -- skip the unbind and drop steps
 --
    if db_id("tempdb_ram") is not null and @dbsize is not null
      begin
        exec @exstat =sp_tempdb unbindall_db, tempdb_ram
        if @exstat != 0
          begin
            print 'Error unbinding Logins from tempdb_ram'
            return(-1)
          end
        drop database tempdb_ram
        if @@error != 0
          begin
            print 'Error dropping database tempdb_ram'
            return(-1)
          end
        exec @exstat = sp_dropdevice 'sybramdsk'
        if @exstat != 0
          begin
            print 'Error dropping device sybramdsk'
            return(-1)
          end
        exec @exstat = xp_cmdshell 'rm -f /dev/shm/sybramdsk'
        if @exstat != 0
          begin
            print 'Error deleting device file /dev/shm/sybramdsk'
            return(-1)
          end
      end
 --
 -- If the device does not exist, build it now
 --
    if not exists (select * from master.dbo.sysdevices where name = 'sybramdsk')
      begin
        if @dbsize is null
          begin
            print 'Error: You must specify a dbsize argument to initially create the sybramdsk device'
            return(-1)
          end
 -- Figure out what the next vdevno is
        select @vdevno = max(convert(tinyint, substring(convert(binary(4),d.low), v.low,1))) + 1
          from master.dbo.sysdevices d, master.dbo.spt_values v
         where v.type = 'E' and v.number = 3
 -- Delete the file that the startup script may have touched there
        exec @exstat = xp_cmdshell 'rm -f /dev/shm/sybramdsk'
        if @exstat != 0
          begin
            print 'Error deleting device file /dev/shm/sybramdsk'
            return(-1)
          end
 -- Do the disk init
        disk init name = 'sybramdsk', physname = '/dev/shm/sybramdsk', vdevno = @vdevno, size = @dbsize, cntrltype = 0
        if @@error != 0
          begin
            print 'Error dropping device sybramdsk'
            return(-1)
          end
      end
 --
 -- If the database does not exist, build it now
 --
    if db_id('tempdb_ram') is null
      begin
        if @dbsize is null
          begin
            print 'Error: You must specify a dbsize aregument to create the tempdb_ram database initially'
            return(-1)
          end
        set @cmdstr = 'begin create temporary database tempdb_ram on sybramdsk = "' + @dbsize + '"'
        set @cmdstr = @cmdstr + ' if @@error != 0 raiserror 20108, "build_tempdb_ram", "failed creating tempdb_ram",0'
        set @cmdstr = @cmdstr + ' end'
        exec (@cmdstr)
        checkpoint master
        if db_id('tempdb_ram') is null
          begin
            print 'Error creating tempdb_ram database'
            return(-1)
          end
      end
 --
 -- Use a cursor and bind all of the DBI% and CSDB% Logins to the new tempdb
 --
    declare lg_names cursor for
    select name
      from master.dbo.syslogins
     where name like 'CSDB%'
        or name like 'DBI%'

     open lg_names
    fetch lg_names into @log_name
    while @@sqlstatus = 0
      begin
        exec @exstat = sp_tempdb bind, lg, @log_name, db, tempdb_ram
        if @exstat != 0
          begin
            raiserror 20108, @sp_name,'failed binding login to tempdb_ram',35
            return(-1)
          end
        fetch lg_names into @log_name
      end
 --
 -- Display the results of this operation
 --
    exec sp_helpdb tempdb_ram
    exec sp_tempdb show, login
end

go
IF OBJECT_ID('dbo.sp_build_tempdb_ram') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.sp_build_tempdb_ram >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_build_tempdb_ram >>>'
go
EXEC sp_procxmode 'dbo.sp_build_tempdb_ram','unchained'
go
0
 

Author Comment

by:mbevilacqua
ID: 19665938
From reading this example, I see that it will create a temporary database for the Linux OS. Can you make this generic for any OS?
0
 
LVL 19

Expert Comment

by:grant300
ID: 19666173
Sure, you can.  I did it for one specific purpose but it can work for anything.  The trick is to deal with the filename and path issues between platforms.  Linux and Unix are pretty easy.  NT is a bit different but the XP_CMDSHELL still works fine there.

What other platforms do you deal with?

Bill
0
 

Author Comment

by:mbevilacqua
ID: 20287072
Bill,
I have not been able to look at this further , but I believe I can take your code you have provided and make it work. I hope Sybase simplifies the dropping of device OS files in the next release.

Thanks for sharing, nice code.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question