Question Re: 'create database' generated by 'load database' has an unnecessary line when database is shrinked in Technology Q&A

'create database' generated by 'load database' has an unnecessary line when database is shrinked

kazuootani — Sat, 03 Feb 2024 22:10:05 GMT

ASE 16.0 SP02

'load database ... with listonly=create_sql' generates DDL ('create database' statement) from a dump file.

When generating 'create database' statement from a shrinked database dump, it carries unnecessary line including 'unknown device'.

*** How to reproduce the issue ***

1. Create a database having data and log devices

1> create database db1 on datadev=200 log on logdev=100
2> go

2. Set "single user" option true to enable database shrinking

1> sp_dboption db1, "single", true
2> go

3. Make database size smaller by shrinking data device

1> alter database db1 off datadev=100
2> go

4. Dump database

1> dump database db1 to "db1.dmp"
2> go

5. Generate 'create database' statement by 'load database ... with listonly=create_sql'

1> load database db1 from "db1.dmp" with listonly=create_sql
2> go
DISK INIT
name = 'datadev'
, physname = '/export/home/ase16/data/datadev.dat'
, size = '200M'
, directio = true
go
DISK INIT
name = 'logdev'
, physname = '/export/home/ase16/data/logdev.dat'
, size = '100M'
, directio = true
go
CREATE DATABASE db1
ON datadev = '100M'
, unknown_device = '100M' ----> This line should be removed.
LOG ON logdev = '100M'
go

I believe the line including ' unknown_device =' is unnecessary and should be removed.
Any plan to fix this behavior in future releases?

Regards,
Kazuo Otani

Re: 'create database' generated by 'load database' has an unnecessary line when database is shrinked

former_member188958 — Tue, 06 Jun 2017 17:15:05 GMT

Hi Kazuo,

The line actually is necessary, as without it the logical page numbers generated for the logdev fragment
would be contiguous with the first fragment, which wouldn't map up with the page numbers in the dump file.

What would be useful would be some way to specify creation of a gap of a particular size, such as on a null device,
and for the "create_sql" to generate output using that option.

You can bring feature requests to the attention of ASE product management by submitting them at
http://ideas.sap.com/sapase.

Cheers,
-bret

Re: 'create database' generated by 'load database' has an unnecessary line when database is shrinked

kazuootani — Wed, 07 Jun 2017 05:42:24 GMT

Hi Bret,

"ddlgen" gives me a "create database" statement that I want.
Let's compare two "create database"s generated by "load database" and "ddlgen"

- load database ... with listonly=create_sql

CREATE DATABASE db1
ON datadev = '100M'
, unknown_device = '100M'
LOG ON logdev = '100M'
go

- ddlgen

CREATE DATABASE db1
ON datadev = '100M' -- 51200 pages
LOG ON logdev = '100M' -- 51200 pages
WITH DURABILITY = FULL
go

I wonder "create database" by "load database ... create_sql" may mislead that database size is 300MB, while actual one is 200MB.
I would like "load database ... create_sql" to generate the same "create database" statement as "ddlgen" does.

Thank you for introducing me feature-request site.
I didn't know that.

Regards,
Kazuo Otani

Re: 'create database' generated by 'load database' has an unnecessary line when database is shrinked

kazuootani — Thu, 08 Jun 2017 06:25:10 GMT

1> create database db1 on datadev=200 log on logdev=100
2> go
1> alter database db1 off datadev=100
2> go
1> select dbid,segmap,lstart,size,vstart from sysusages where dbid=db_id("db1")
2> go
 dbid   segmap      lstart     size      vstart
 ------ ----------- ---------- --------- -----------
      8           3          0     51200           0
      8           0      51200     51200       51200
      8           4     102400     51200           0

Re: 'create database' generated by 'load database' has an unnecessary line when database is shrinked

kazuootani — Fri, 09 Jun 2017 06:04:26 GMT

Mark,

> I suggest you run the ddlgen generated create database command and then try to load your db dump file into said database ... it should generate an error telling you the the database is too small.

I tried it.
Plaese see command sequence.

1> create database db1 on datadev=200 log on logdev=100
2> go
1> alter database db1 off datadev=100
2> go
1> dump database db1 to "db1.dmp"
2> go
1> drop database db1
2> go
1> create database db1 on datadev=100 log on logdev=100 for load
2> go
1> load database db1 from "db1.dmp"
2> go
1> online database db1
2> go

load database works fine.
It recognizes actual database size even if dump file has a hole in page numbering.
So, it looks ddlgen generated create database has no problem.

After loading, sysusages shows the same result I posted above.

Re: 'create database' generated by 'load database' has an unnecessary line when database is shrinked

kazuootani — Mon, 12 Jun 2017 06:19:11 GMT

Does create database command generated by load database ... with listonly=create_sql stay unchanged or go to CR when sysusages has a hole in page numbering?