mbevilacqua
asked on
Sybase script to create device
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
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
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...
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...
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
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
ASKER
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.
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.
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
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
ASKER
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'
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'
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
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
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!
.. 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!
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
What other platforms do you deal with?
Bill
ASKER
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.
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.
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